MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Repeat Conditional Formatting

Posted by Alan G on April 30, 2001 12:31 PM

I want to repeat the following conditional format on a number of rows:

If C1 <> F1 then use Red Font

How can I copy this conditional format on each row without manually writing the format for each one?

Posted by Kevin James on April 30, 2001 12:39 PM

Read your help file on copying cells.

Posted by Alan G on April 30, 2001 12:52 PM

I suppose a little more explaination is in order. My condition is as follows

cell value is not equal to concatenate($J$3," ",$k$3)

When I copy the format to row 4, it copies the original comparison (for row 3).

If I change the comparison to $J3,$k3, I get the same result.

Posted by IML on April 30, 2001 12:55 PM

One way is to select your cell and hit the format painter (paint brush icon) and then highlight the cells you want to copy it to.
Another way is to copy your format cell, and paste special selecting formats.
Make sure if you want all your formats to be dependent on those two cells, you use absolute value (ie $C$1 <> $f$1)

Good luck,

Posted by Kevin James on April 30, 2001 1:01 PM


sorry for being so terse. Would you please email me an example. I am not following your explanation.


Posted by Dave Hawley on April 30, 2001 1:31 PM

Hi Alan

Before placing in your Conditional Formatting, select the cells you want to apply it to. Then type the Formula or Condition using the Active Cell as the reference.

For example it I select the cells A1:C20 and use: Cell value is, not equal to: D1 & " " & E1
Set a Format and click OK.
Cell A2 Conditional Formatt wil be D2 & " " & E2
Cell A3 Conditional Formatt wil be D3 & " " & E3
Cell B1 Conditional Formatt wil be E1 & " " & F1
Cell B2 Conditional Formatt wil be E2 & " " & F2
..and so on.


OzGrid Business Applications

Posted by Alan G on April 30, 2001 1:36 PM


When I tried to copy my example to a file I could email you, I figured out why EXCEL was doing what it was doing and was able to figure out a way around it (although its clunky).

Thanks anyway


Posted by Alan G on May 01, 2001 12:36 PM


When I tried that, the condition format automatically places my condition in quotes as follows:

="j3 & "" "" & k3"

Every subsequent row is the same j3 and k3


Posted by Dave Hawley on May 01, 2001 2:09 PM

Alan, that's because i forgot to place in the equal sign :o) should have been:

=D1 & " " & E1


OzGrid Business Applications