Stopping Hidden Cells being Conditionally Formatted

[JW]

Hi, I've got a worksheet showing figures for 2002 that has the corresponding values for 2001 hidden on alternate rows ie
row 10 2002 figs
row 11 2001 figs(hidden)
row 12 2002 figs
row 13 2002 figs (hidden)
The conditional formatting for 2002 is based on comparisons with 2001,but I want to leave 2001 unformatted. When I drag the conditional formatting across the 2002 rows it also applies to the hidden 2001 rows. Is there a way to avoid this, so the hidden cells don't pick up the fromatting meant for the visible cells?

 

[Ian Mac]

On 2002-09-02 02:09, JW wrote:
Hi, I've got a worksheet showing figures for 2002 that has the corresponding values for 2001 hidden on alternate rows ie
row 10 2002 figs
row 11 2001 figs(hidden)
row 12 2002 figs
row 13 2002 figs (hidden)
The conditional formatting for 2002 is based on comparisons with 2001,but I want to leave 2001 unformatted. When I drag the conditional formatting across the 2002 rows it also applies to the hidden 2001 rows. Is there a way to avoid this, so the hidden cells don't pick up the fromatting meant for the visible cells?

Here are 2 ways to do it,

1) the easiest way would be to copy the row and Paste Special> Formatting to the row 2 down from that.

2) If you wish to drag the formatting, you could add another condition before your other one(s):

Highlight the area. in this case I've started at B11 and put:

=$A11=2001

Add don't do any formatting. Change the 2001 to what would indicate that it is 2001 figures (I think 2001 would do though).

Hope this helps, any questions ask.

 

[JW]

Adding the extra condition has done it, thanks Ian.