I have code taken from another source (not sure if Im allowed to say what website I got it from - but incase not - I'd like to thank the author)
Basically it highlights the top cell in a filtered range, if there is a filter in place on that column. So far so good. The problem I have is that If I then open another workbook, its applying the same rule. I want the code only to apply to the original workbook. I believe the problem is that the code references activesheet and therefore is triggered by any other open workbook. How can I change the code so that it only runs on the workbook for which it was intended. The code is as follows:
In Thisworkbook
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Application.Windows("Error Logs.xlsm").Activate
If Sh.AutoFilterMode Then ColorDisplayFilter
End Sub
and then in a separate module
Sub ColorDisplayFilter()
Application.Windows("Error Logs.xlsm").Activate
Dim flt As Filter
Dim iCol As Integer
Dim lRow As Long
iCol = 0
lRow = ActiveSheet.AutoFilter.Range.Row
Application.EnableEvents = False
For Each flt In ActiveSheet.AutoFilter.Filters
iCol = iCol + 1
If flt.On Then
Cells(lRow, iCol).Interior.ColorIndex = 26
Else
Cells(lRow, iCol).Interior.ColorIndex = 3
End If
Next flt
Application.EnableEvents = True
End Sub
I've tried changing activesheet to thisworkbook but that returns an error?
Any help would be greatly appreciated.
Thank you in advance.
Bookmarks