Conditional Format Columns Only When Filter Applied

[mnyankee]

I need help figuring out how to conditionally format a column or columns based on another column being filtered or not. In other words if the Match column is filtered for either true or false, then I need formatting applied to the Value 1 and Value 2 columns and then have that formatting removed when the Match column is not being filtered. Is this possible? Thanks in advance for any help you can provide.

Value 1	Value 2	Match
1	1	TRUE
2	3	FALSE
3	0	FALSE
4	2	FALSE
5	5	TRUE
6	5	FALSE
7	4	FALSE
8	8	TRUE
9	1	FALSE
10	10	TRUE

 

[kevin9999]

Is this possible?

Yes. There's probably more elegant ways of doing this than what I propose, but give this a try on a copy of your workbook until something better comes along. Firstly, you need to be able to trigger an event when the filter changes. The way I usually do this is with a Worksheet_Calculate() event - coupled with a CountA() formula in a spare cell on your sheet. I've used cell D1 looking at column A (you can hide column D if you like). So the basic sheet looks like this:

mnyankee.xlsm
	A	B	C	D
1	Value 1	Value 2	Match	11
2	1	1	TRUE
3	2	3	FALSE
4	3	0	FALSE
5	4	2	FALSE
6	5	5	TRUE
7	6	5	FALSE
8	7	4	FALSE
9	8	8	TRUE
10	9	1	FALSE
11	10	10	TRUE
Sheet1

Cell Formulas
Range	Formula
D1	D1	=COUNTA(A:A)

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Sheet1!$A$1:$C$11	D1

Next, you place a Worksheet_Calculate() event code in the sheet module of the sheet in question. If you're unfamiliar with how to do this, right click on the sheet tab name, select View Code and copy/paste the code below into the window that appears on the right.

Private Sub Worksheet_Calculate()
    Application.EnableEvents = False
    Dim r As Range, c As Range, LRow As Long, i As Long, s As String
    LRow = Cells(Rows.Count, "C").End(xlUp).Row
    Set r = Range("A2:C" & LRow)
    For Each c In Range("C1:C" & LRow)
        If c.EntireRow.Hidden = False Then i = i + 1
    Next c
    With r
        If i = LRow Then
            .Interior.Color = xlNone
            .Font.ColorIndex = 0
            .Font.Bold = False
        Else
            With .Columns("A:B")
                For Each c In Range("C2:C" & LRow)
                    If c.EntireRow.Hidden = False Then
                        s = UCase(c.Value)
                        Exit For
                    End If
                Next c
                If s = "TRUE" Then
                    .Interior.Color = vbGreen
                    .Font.Bold = True
                Else
                    .Font.Color = vbRed
                    .Font.Bold = True
                End If
            End With
        End If
    End With
    Application.EnableEvents = True
End Sub

You haven't indicated what formats you want depending on whether True or False is selected, so for demonstration purposes I've made the cell interior green for true, and the font red for false. When you filter for True, you'll get this:

mnyankee.xlsm
	A	B	C	D
1	Value 1	Value 2	Match	11
2	1	1	TRUE
6	5	5	TRUE
9	8	8	TRUE
11	10	10	TRUE
Sheet1

Cell Formulas
Range	Formula
D1	D1	=COUNTA(A:A)

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Sheet1!$A$1:$C$11	D1

When you filter for false, you'll get this:

mnyankee.xlsm
	A	B	C	D
1	Value 1	Value 2	Match	11
3	2	3	FALSE
4	3	0	FALSE
5	4	2	FALSE
7	6	5	FALSE
8	7	4	FALSE
10	9	1	FALSE
Sheet1

Cell Formulas
Range	Formula
D1	D1	=COUNTA(A:A)

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Sheet1!$A$1:$C$11	D1

The formatting is removed when no filter is set. Obviously, you can change the formats to whatever you want. Here's a link to the demo file: mnyankee.xlsm