How to set cell back color for a cell that is hidden via an auto filter

[ttt123]

I can't set the cell back color for a cell that is hidden via an auto-filter. Does anyone know how to change the cell color without clearing the auto-filter?

Sub SetCellBackColorForAutoFilteredCell()
    Dim r As Range
    Dim lo As ListObject
    Set r = Range("A1:A3")
    r.Value = Application.WorksheetFunction.Transpose(Array("header", 1, 2))

    Set lo = ActiveSheet.ListObjects.Add(xlSrcRange, r, , xlYes)
    
    'Hide the cell with value 2 via AutoFilter. Row 3 will be hidden.
    lo.Range.AutoFilter Field:=1, Criteria1:="1"
    
    'Set the cell color for rows 2 and 3
    With lo.DataBodyRange.Interior
        .Color = RGB(255, 0, 0)
    End With

    'Clear AutoFilter to unhide row 3
    lo.Range.AutoFilter Field:=1
    
    'Note that row 3 did not get its cell color changed because it was hidden via the AutoFilter
End Sub

 

[Michael M]

I don't believe you can do that to a cell hidden by autofilter...but...why not colour the cell before applying the filter ??

 

[ttt123]

I don't believe you can do that to a cell hidden by autofilter...but...why not colour the cell before applying the filter ??

that's too bad. The autofilter would potentially be set by the user before running the macro so no option to set the color first

 

[Peter_SSs]

Not a strong area of mine so don't know if there is a direct way to do what you want, but would this feasible for you?

Dim cell As Range
.
.    
For Each cell In lo.DataBodyRange
  cell.Interior.Color = RGB(255, 0, 0)
Next cell

 

[ttt123]

Not a strong area of mine so don't know if there is a direct way to do what you want, but would this feasible for you?

Dim cell As Range
.
.    
For Each cell In lo.DataBodyRange
  cell.Interior.Color = RGB(255, 0, 0)
Next cell

Hi Peter, thanks for your reply, but unfortunately that won't work.

The issue is that if a cell is hidden by an auto-filter then setting cell.Interior.Color does not actually do anything and the color remains the same as it was before. It looks like if you want to change the color of a cell that is hidden by an auto-filter, you have to first clear the filter, so that the cell becomes visible, and then set the color.

 

[Peter_SSs]

Hi Peter, thanks for your reply, but unfortunately that won't work.

"Won't work", as opposed to "didn't work", implies you just 'know' that and didn't actually try it. It worked for me.

 

[ttt123]

"Won't work", as opposed to "didn't work", implies you just 'know' that and didn't actually try it. It worked for me.

Sorry about that Peter, you are indeed correct.

I assumed Range.Interior.Color would behave the same regardless of whether the range had a single cell in it or multiple cells in it. It appears that it doesn't like the property if you have a mix of visible and hidden rows and iterating through the individual cells in the range and setting the property individually does not seem to have the issue.

Thanks for your help.

 

[Peter_SSs]

You are welcome. Always worth trying suggestions.