Stopping Hidden Cells being Conditionally Formatted

JW

Board Regular
Joined
Mar 17, 2002
Messages
72
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?
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Ian Mac

MrExcel MVP
Joined
Feb 20, 2002
Messages
1,174
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

Board Regular
Joined
Mar 17, 2002
Messages
72
Adding the extra condition has done it, thanks Ian.
 

Forum statistics

Threads
1,143,613
Messages
5,719,710
Members
422,242
Latest member
hishamkhatri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top