Background cell color on filter in vba

[colombo2003]

Hi everybody,

I have been stuggling for same time now but I don't now how to do that... So, I need your help.

Column used: A to Q
Row 1: header
Row 1: I click on sort&Filter ribbon icon, then filter (so filter is on but not in use).

I want to change the background color in a cell (e.g. P1), when I select, I click on filter and I apply one or more data as criteria.

Somebody can help me, please? Thx

I tried this:
In module:


Sub testF()
With ActiveSheet
If .AutoFilterMode = True And .FilterMode = True Then
With Range("P1").Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = RGB(50, 8, 202)
.TintAndShade = 0
.PatternTintAndShade = 0
End With
end if
end sub


In sheet1:


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell.Address = "$P$1" Then
testF
End If
...
...
...
end sub

[Kenneth Hobs]

Welcome to the forum!

It "works" as you designed it. In the Immediate Window, you can see why it might not be "working" as you think.

?Activesheet.FilterMode 'Press Enter see the result.

Tip: You are missing an End With in TestF(). ActiveCell can simply be Target.

As coded, once it fully executes the IF() in WorkSheet_SelectionChange(), P1 would always be that interior color.

[colombo2003]

My mistake: I forgot to add "End With" in this post (in code exist).
Change ActiveCell by Target.
But doesn't works for me...

[Kenneth Hobs]

I don't know what you mean by "doesn't works for me". Obviously, it "worked" perfectly for me or I would not have posted what I did. As I explained, setting the filter may not produce a True value for your ActiveSheet.FilterMode like you think. e.g. If Column A values are say 2, then just selecting 2 in the filter selects all which makes FilterMode False. If values are 1,2,3 for A2:A4 and filter of 2, then FilterMode is True. So, FilterMode in an If() is more problematic than one think and especially so if data is not known.

You will need to post your file or a simplified obfuscated version if you need more detailed troubleshooting. Click Go Advanced in lower right of a replay, and then the paperclip icon in the toolbar to Browse and Upload a file.