Highlight Cells Macro


Posted by Peter Crymble on October 19, 2001 5:36 AM

Hi,
I posted this question earlier but havent figured it out yet!I have a macro which highlights the back color of cells yellow. Simple u think - maybe not!

There is a filter running on one column. I need the macro to highlight the top 2 visible rows, no matter what row they are. For example, the macro below highlights one row1. However, if the filter is applied and the first visible row is then say 23, the macro doesnt work.

Someone suggested going to the option 'view visible cells only' but this doesnt work either.

Here is my code:

'HIGHLIGHT 1
Range("B2:AV2").Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With

NB-note the range which is incorrect - always highlights range B2:AV2 instead of the top visible row!!

Thanks for all your help.
Peter



Posted by Juan Pablo on October 19, 2001 6:05 AM

Change Range("A2:AV2") to

Range("A2:A40").SpecialCells(xlCellTypeVisible).Range("A1:AV2")

You need to adjust the first Range, A2:A40 to your needs.

Juan Pablo