How to change the background color of the filtered data in a Column

[mebro]

Hi All,

Help me regarding filter option, If I filter the data in any Column of excel sheet, I want to change filtered cell background color for that filtered values, if this is possible please help me else suggest me the best approach for the same

my intention is, if there is any filter on the column then I want to show that differently.

 

[James006]

Hi,

In order to achieve what you need ... you will have to use an Event macro ...:wink:

Are you familiar with these macros ???

 

[Timbo50]

Hi James, thanks for your response. I have used macros but am unsure what is required here. Can you also tell me why this is happening as I have other formulas that are conditionally formatted according to their value without any problems?

 

[mebro]

Hi James,

thanks for your quick response, can you please help me with macro code for this.

 

[James006]

Hi James,

thanks for your quick response, can you please help me with macro code for this.

No problem ...

Can you quickly describe your sheet ...

are your headers in row 1 ?
how many columns do you use ?
which column is the one used to filter ?

HTH

 

[mebro]

yes, I have Range table with 4 columns with row 1 as the header. i want to filter column 3

 

[James006]

Hello,

In order to change the interior color of the filtered rows ... you could use following:

Sub AddColorFilteredRows()
Dim wks As Worksheet
Dim rng As Range
Dim rce As Range
Dim lrn As Long
Dim lrf As Long


    Set wks = ActiveSheet


    lrn = Application.WorksheetFunction.CountA(Range(Cells(1, 1), Cells(1000, 1)))
    lrf = wks.Range("A" & Rows.Count).End(xlUp).Row
    
      For Each rce In wks.Range("A2:D" & lrn)
        If rce.Interior.Color <> xlNone Then rce.Interior.Color = xlNone
      Next rce
    
    If lrn = lrf Then MsgBox " No Criteria Selected in Autofilter...": Exit Sub
    
    lrf = wks.Range("A" & Rows.Count).End(xlUp).Row
    Set rng = wks.Range("A2:D" & lrf)
    rng.SpecialCells(xlCellTypeVisible).Interior.Color = vbYellow
    
End Sub

Hope this will help

 

[mebro]

Thanks for your help,it helped me alot .

 

[James006]

Thanks for your help,it helped me alot .

Glad you could fix your problem ...:wink:

Thanks ... for your thanks ...