If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Conditional Formatting Separate Rows
I need to set-up three formali in each row of a 1000 row spreadsheet. The
three forumli a =A4="x" then B4 : P4 strickthrough, green font =B4Today() B4:P4 Font Red =R4=True B4:P4 Font Purple Italic. I enter that in Row 4 , now I need to repeat this for every row, but every attempt fails to change the row identifier- thus every row ends up refering to row 4 only. Hope do I copy this instead hand writing each row- 1000 rows? |
#2
|
|||
|
|||
Conditional Formatting Separate Rows
Just go into Conditional Formatting again for that cell and check that
Excel has not added $ in front of the 4 in the formulae - if it has, then remove them. Then you can repeat that CF for other cells by using the Format Painter - if you double-click on the icon you can then apply the format to many other cells by means of a single-click, and then press Esc when you have finished. Hope this helps. Pete On Sep 22, 8:30*pm, Pete wrote: I need to set-up three formali in each row of a 1000 row spreadsheet. *The three forumli a * =A4="x" then B4 : P4 strickthrough, green font =B4Today() B4:P4 Font Red =R4=True B4:P4 Font Purple Italic. I enter that in Row 4 , now I need to repeat this for every row, but every attempt fails to change the row identifier- thus every row ends up refering to row 4 only. *Hope do I copy this instead hand writing each row- 1000 rows? |
#3
|
|||
|
|||
Conditional Formatting Separate Rows
Unfortunately, I does not work totally. The designated rows change (the rows
which the formats effect), however the formula stays the same for every row. That is not what we want. I need to have every row effect the rows designated, so if an x is in A4, only cells b44 are effected. And if R7 = True, then only cells b77 are effected. Etc. Each row stands alone as a conditional format, but I can't find a way to make one row correct for our conditional formate and then copy that to each of the 1000 lines I need without having to edit every row individually. I don't have that much time. Thanks, Pete -US. "Pete_UK" wrote: Just go into Conditional Formatting again for that cell and check that Excel has not added $ in front of the 4 in the formulae - if it has, then remove them. Then you can repeat that CF for other cells by using the Format Painter - if you double-click on the icon you can then apply the format to many other cells by means of a single-click, and then press Esc when you have finished. Hope this helps. Pete On Sep 22, 8:30 pm, Pete wrote: I need to set-up three formali in each row of a 1000 row spreadsheet. The three forumli a =A4="x" then B4 : P4 strickthrough, green font =B4Today() B4:P4 Font Red =R4=True B4:P4 Font Purple Italic. I enter that in Row 4 , now I need to repeat this for every row, but every attempt fails to change the row identifier- thus every row ends up refering to row 4 only. Hope do I copy this instead hand writing each row- 1000 rows? |
#4
|
|||
|
|||
Conditional Formatting Separate Rows
Highlight the block of cells B4:P1000 with B4 being the active cell.
Click on Format | Conditional Formatting, select Formula Is in the first box and use this formula: =$A4="x" then click the Format button and set the format to Strikethrough, Green font. Click OK, then Add. This time the formula will be: =$B4TODAY() then set the format to Red font. Click OK and then Add again, and use this formula: =$R4=TRUE then choose your purple italic font. Click OK twice to exit the dialogue box, and you will now have set it up for all those rows as Excel will adjust the row (but not the column because of the $ symbol in front) to suit. Hope this helps. Pete On Sep 23, 8:47*pm, Pete wrote: Unfortunately, I does not work totally. *The designated rows change (the rows which the formats effect), however the formula stays the same for every row. * That is not what we want. I need to have every row effect the rows designated, so if an x is in A4, only cells b44 are effected. *And if R7 = True, then only cells b77 are effected. Etc. Each row stands alone as a conditional format, but I can't find a way to make one row correct for our conditional formate and then copy that to each of the 1000 lines I need without having to edit every row individually. * I don't have that much time. Thanks, Pete -US. "Pete_UK" wrote: Just go into Conditional Formatting again for that cell and check that Excel has not added $ in front of the 4 in the formulae - if it has, then remove them. Then you can repeat that CF for other cells by using the Format Painter - if you double-click on the icon you can then apply the format to many other cells by means of a single-click, and then press Esc when you have finished. Hope this helps. Pete On Sep 22, 8:30 pm, Pete wrote: I need to set-up three formali in each row of a 1000 row spreadsheet. *The three forumli a * =A4="x" then B4 : P4 strickthrough, green font =B4Today() B4:P4 Font Red =R4=True B4:P4 Font Purple Italic. I enter that in Row 4 , now I need to repeat this for every row, but every attempt fails to change the row identifier- thus every row ends up refering to row 4 only. *Hope do I copy this instead hand writing each row- 1000 rows?- Hide quoted text - - Show quoted text - |
Thread Tools | |
Display Modes | |
|
|