shade hidden rows

T

TJ

How can I set up my worksheets so that rows that are hidden will be shaded in
if they are unhidden?
 
M

Mark Lincoln

TJ,

Set the cell shading for the rows before hiding them. If they're
subsequently unhidden, the shading will be there.
 
T

TJ

I was hoping for some type of conditional formatting or code that would
automate this for me.
 
B

Bernie Deitrick

TJ,

If they are hidden using hide rather than filters, then a macro like this
will shade the hidden rows:

Sub NewSub()
Application.ScreenUpdating = False
Cells.Interior.ColorIndex = 3
Cells.SpecialCells(xlCellTypeVisible).Interior.ColorIndex = xlNone
End Sub

HTH,
Bernie
MS Excel MVP
 
T

TJ

That is the effect I want. How can I make it so that when I hide the cells
they are shaded without me having to run the macro every time I hide cells?
I would like to have the act of hiding the cells trigger the shading.
 
B

Bernie Deitrick

TJ,

Sorry, there is no event that Excel can use to tell when you hide a row, so
once you've hidden the rows, you need to manually run the macro.

Bernie
 
M

Mark Lincoln

Bernie, might TJ be able to highlight the rows to hide and then run a
macro to both shade and hide them? That would probably be the closest
thing to want he'd like to do.
 
Top