Conditional Formatting Separate Rows

P

Pete

I need to set-up three formali in each row of a 1000 row spreadsheet. The
three forumli are:

=A4="x" then B4 : P4 strickthrough, green font
=B4<Today() 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?
 
P

Pete_UK

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
 
P

Pete

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 b4:p4 are effected. And if R7 = True, then only cells b7:p7 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.
 
P

Pete_UK

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:

=$B4<TODAY()

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top