+ Reply to Thread
Results 1 to 2 of 2

Excel 2007 : Autofilter Colour Heading Cell with filter

Hybrid View

  1. #1
    Registered User
    Join Date
    09-23-2010
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    8

    Autofilter Colour Heading Cell with filter

    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.

  2. #2
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,342

    Re: Autofilter Colour Heading Cell with filter

    hi ziggadebo
    did you try..
    thisworkbook.activesheet....
    If the solution helped please donate to RSPCA

    Site worth visiting: Rabbitohs

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1