Excel Highlight Macro

Posted by Peter Crymble on October 15, 2001 10:09 AM

Hi,

I have a spreadsheet which shows a person, their department and points scored for an incentive scheme. I have a macro that filters by department and sorts the points - so that the top scorers for each department can be seen.

However, I need to highlight the top 2 fields for each department. e.g. when i filter by say the technology department, the top two rows should be highlighted. I can do this by changing the bckground color but heres my problem. The highlight macro only works if rows 1 and 2 are displayed. What happens when the filter is on and say rows 50 and 60 are shown. I cant write individual macros as the row numbers will change. How do I highlight the top 2 rows, no matter what row number they are?

Thanks for all your help,
Peter

Posted by Gyula Lorant on October 15, 2001 2:27 PM

Sounds like you should be able to set a range for "visible cells only" in your code. You can get the code from the macro recorder by going to Edit>GoTo>Special>VisibleCellsOnly.

If cannot do, post your code.

Posted by Peter Crymble on October 18, 2001 7:10 AM

Thanks 4 ur reply.

My code is too long 2 post here but the highlight section is as follows:
'HIGHLIGHT 1
Range("B2:AV2").Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With

NOTE the range. These are the first 2 lines of the worksheet. However,when a filter is applied obviously rows 1 and 2 are no longer present. I want to highlight the top 2 rows whatever the case, no matter what filter is on. How do I do this?? I couldnt get your previous suggestion to work. Thanks for all you help so far, Peter