How do I copy conditional formatting that changes lines

[Guest]

Hi there

I would like to copy conditional formatting in one cell (i.e. that says
format C1 if is isn't equal to A1) to another row - say row 2. Instead of the
conditional formatting now saying if C2 isn't equal to A@ then format, it
just keeps it referring to A1 as in the first formula.

Is there any way to get it to change the row number accordingly, (as excel
does with other regular formulas) rather that having to go into each cell
individually and changing the numbers?

Any help would be greatly appreciated!

 

[Rick Rothstein \(MVP - VB\)]

Are you asking how to (easily) make the conditional formatting for each cell
in Column 2 be conditional on its value not being equal to the value in A1?
Click the "C" in the column header to select the entire column, select
Format/ConditionalFormatting from Excel's menu, select "Formula Is" from the
first drop down and place this formula in the second field...

=AND(I1<>"",I1<>$A$1)

then select a format condition and, finally, OK your way back to the
spreadsheet. The above formula makes blank cells and cells with the same
value as A1 remain non-formatted; however, any cells with a value different
from A1 will be formatted.

Rick

 

[David McRitchie]

You would have to put the conditional format formula into cell C2.
and it would be the active cell when you code the formula.
http://www.mvps.org/dmcritchie/excel/condfmt.htm

 

[David Biddulph]

You need to look at the difference between relative and absolute addressing,
and adjust the $ symbols accordingly.