Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

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?


Re: Repeat Conditional Formatting

Posted by Kevin James on April 30, 2001 12:39 PM
Read your help file on copying cells.

Re: Repeat Conditional Formatting

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.


Re: Repeat Conditional Formatting

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,
Ian


Re: Repeat Conditional Formatting

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

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

Thanks,
Kevin


Re: Repeat Conditional Formatting

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.


Dave


OzGrid Business Applications


Re: Repeat Conditional Formatting

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

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

Alan


Re: Repeat Conditional Formatting

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


Dave:

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

Alan


Re: Repeat Conditional Formatting

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


Dave

OzGrid Business Applications


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.