+ Reply to Thread
Results 1 to 5 of 5

Column header to fill with a colour when filter applied?

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    02-20-2017
    Location
    UK
    MS-Off Ver
    2010
    Posts
    133

    Column header to fill with a colour when filter applied?

    I have various buttons on a home tab which filter Sheet2 depending on which button is clicked. The filters hide columns on Sheet2, amongst other things.

    Would it be possible to add some vba to the filter code which colours in a certain column header(s)?

    For example here's my code for one of the buttons:

    Sub ActivateAndSortLastWeek()
    Sheet2.Range("B:D,L:R,T:U,X:X").EntireColumn.Hidden = True
    Dim lr As Long
        lr = Sheet2.Range("A" & Rows.Count).End(xlUp).Row
        Sheet2.Activate
        With Sheet2.Range("A1:X" & lr)
            .AutoFilter Field:=1, Criteria1:=xlFilterLastWeek, Operator:=xlFilterDynamic
        End With
    End Sub
    For the scenario above, I'd like S1, V1 and W1 changed from their current colour to orange, so it is clear for the user to understand which columns they need to fill in at which time.

    I'd like the column headers to then revert to their old colour when a reset button is clicked... perhaps some reset colour code could be added to the below?:

    Sub ShowAllData()
    
    Sheets("Sheet2").Select
    
    ActiveSheet.AutoFilterMode = False
    ActiveSheet.Cells.EntireColumn.Hidden = False
    
    End Sub
    Thanks in advance.

  2. #2
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Column header to fill with a colour when filter applied?

    Hi,

    To the first macro you need only add this
    Range("S1,V1,W1").Interior.Color = 4626167
    and then to clear them again
    Range("S1,V1,W1").Interior.ColorIndex = xlcolorindexautomatic
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  3. #3
    Forum Contributor
    Join Date
    02-20-2017
    Location
    UK
    MS-Off Ver
    2010
    Posts
    133

    Re: Column header to fill with a colour when filter applied?

    Great, thanks. Is there a list of colour ID's anywhere? I should have said but the colour I'd like them to revert to is one of the grey theme colours (White, Background 1, Darker 25%) not just the standard white background.

    EDIT: I think I can locate the RGB colour code, but this doesn't seem to work when added to the VBA, is it a different ID that is used?

  4. #4
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Column header to fill with a colour when filter applied?

    If you know the RGB values you can use them like this
    Range("S1,V1,W1").Interior.Color = RGB(25, 25, 25)

  5. #5
    Forum Contributor
    Join Date
    02-20-2017
    Location
    UK
    MS-Off Ver
    2010
    Posts
    133

    Re: Column header to fill with a colour when filter applied?

    Great stuff useful to know, thanks for your help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 0
    Last Post: 10-10-2014, 02:02 PM
  2. VBA to find first row under header, after filter has been applied.
    By Chris Acheson in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-28-2014, 11:50 AM
  3. Change cell colour when applied filter is on
    By DanielPodo in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-02-2012, 09:30 AM
  4. [SOLVED] Need Macro to Select All Files in a Column After Filter Applied
    By Gard5096 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 09-12-2012, 05:33 PM
  5. Replies: 5
    Last Post: 05-23-2012, 10:33 AM
  6. Colour empty cells if under Column Header x
    By Jessica.Bush in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 08-12-2010, 08:36 AM
  7. Filter columns by fill colour
    By Ascesis in forum Excel General
    Replies: 3
    Last Post: 08-06-2006, 05:50 PM

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