Filter by Colour - conditional formating

[kd]

Hi,

I have created a condition in conditional formatting highlighting a specific
color. How can I filter by that colour (Red in this case).

Any help will be greatly appreciated.

Regards,
KD

 

[Jacob Skaria]

You can try out the below solution which uses a helper column and a UDF .

From workbook launch VBE using Alt+F11. From menu Insert a Module and paste
the below function.Close and get back to workbook and try the below formula.

In the helper column use the below formula and copy down as required. Cell
E1 is the first cell in the column which is conditional formatted...

=IF(getcfcolorindex(E1)=3,"Filter by red","")

Function GetCFColorIndex(C As Range) As Variant
Dim intCount As Integer, FC As FormatCondition, blnMatch As Boolean
If C.Count <> 1 Then Exit Function
For intCount = 1 To C.FormatConditions.Count
'Loop through each Contidional Formatting
Set FC = C.FormatConditions(intCount)
Application.Volatile
If FC.Type = 1 Then
'Handle Type1-xlExpression (If 'Cell Value Is')
Select Case FC.Operator
Case xlBetween '1
If C.Value >= GetCFV(FC.Formula1) And C.Value _
<= GetCFV(FC.Formula2) Then blnMatch = True: Exit For
Case xlNotBetween '2
If C.Value < GetCFV(FC.Formula1) Or C.Value _

GetCFV(FC.Formula2) Then blnMatch = True: Exit For

Case xlEqual '3
If C.Value = GetCFV(FC.Formula1) Then blnMatch = True: Exit For
Case xlNotEqual '4
If C.Value <> GetCFV(FC.Formula1) Then blnMatch = True: Exit For
Case xlGreater '5
If C.Value > GetCFV(FC.Formula1) Then blnMatch = True: Exit For
Case xlGreaterEqual '6
If C.Value >= GetCFV(FC.Formula1) Then blnMatch = True: Exit For
Case xlLess '7
If C.Value < GetCFV(FC.Formula1) Then blnMatch = True: Exit For
Case xlLessEqual '8
If C.Value <= GetCFV(FC.Formula1) Then blnMatch = True: Exit For
End Select
Else
'Handle Type2-xlExternal (If 'Formula Is')
If Evaluate(Application.ConvertFormula( _
Application.ConvertFormula(FC.Formula1, xlA1, xlR1C1), _
xlR1C1, xlA1, , C)) Then blnMatch = True: Exit For
End If
Next

If blnMatch Then GetCFColorIndex = FC.Interior.ColorIndex
End Functio
'-------------------------------------------------------------------------------
Function GetCFV(strData As Variant)
'Get text string or numeric from CF formula
If Not IsNumeric(strData) Then
GetCFV = Mid(strData, 3, Len(strData) - 3)
Else
GetCFV = CDbl(strData)
End If
End Function


If this post helps click Yes

 

[kd]

Hi Jacob,

Thank you for the prompt reply.

The result is showing "Filter by red" against all the data, whereas the
condition is true in few cells only. Can you help?

Regards,
KD

 

[Jacob Skaria]

Post your conditional formatting formula and the UDF formula which you tried
so as to recreate.

If this post helps click Yes

 

[kd]

Conditional formatting applied in Cell M2.

That is if Cell Value is not equal to =B2 - Format-->Patterns-->Cell
Shading-->Red color.

UDF in Column W: =IF(getcfcolorindex(M2)=3,"Filter by red","")

Regards,
KD

 

[keiji kounoike]

Put the same formula as used in conditional formatting in a helper
column. the value of the cells in a helper column in the same row with
colored cells will be true. then you could filter by that value, true.

Keiji

 

[Jacob Skaria]

Try out the modified version..



Function GetCFColorIndex(c As Range) As Variant
Dim intCount As Integer, FC As FormatCondition, blnMatch As Boolean
If c.Count <> 1 Then Exit Function
For intCount = 1 To c.FormatConditions.Count
'Loop through each Contidional Formatting
Set FC = c.FormatConditions(intCount)
Application.Volatile
If FC.Type = 1 Then
'Handle Type1-xlExpression (If 'Cell Value Is')
Select Case FC.Operator
Case xlBetween '1
If c.Value >= GetCFV(FC.Formula1, c) And c.Value _
<= GetCFV(FC.Formula2, c) Then blnMatch = True: Exit For
Case xlNotBetween '2
If c.Value < GetCFV(FC.Formula1, c) Or c.Value _

GetCFV(FC.Formula2, c) Then blnMatch = True: Exit For

Case xlEqual '3
If c.Value = GetCFV(FC.Formula1, c) Then blnMatch = True: Exit For
Case xlNotEqual '4
If c.Value <> GetCFV(FC.Formula1, c) Then blnMatch = True: Exit For
Case xlGreater '5
If c.Value > GetCFV(FC.Formula1, c) Then blnMatch = True: Exit For
Case xlGreaterEqual '6
If c.Value >= GetCFV(FC.Formula1, c) Then blnMatch = True: Exit For
Case xlLess '7
If c.Value < GetCFV(FC.Formula1, c) Then blnMatch = True: Exit For
Case xlLessEqual '8
If c.Value <= GetCFV(FC.Formula1, c) Then blnMatch = True: Exit For
End Select
Else
'Handle Type2-xlExternal (If 'Formula Is')
If Evaluate(Application.ConvertFormula( _
Application.ConvertFormula(FC.Formula1, xlA1, xlR1C1), _
xlR1C1, xlA1, , c)) Then blnMatch = True: Exit For
End If
Next

If blnMatch Then GetCFColorIndex = FC.Interior.ColorIndex
End Functio
'-------------------------------------------------------------------------------
Function GetCFV(strData As Variant, c As Range)
'Get text string or numeric from CF formula
If IsNumeric(strData) Then
GetCFV = CDbl(strData)
ElseIf InStr(strData, Chr(34)) Then
GetCFV = Mid(strData, 3, Len(strData) - 3)
Else
GetCFV = Range(Mid(Application.ConvertFormula( _
Application.ConvertFormula(strData, xlA1, xlR1C1), _
xlR1C1, xlA1, , c), 2))
End If
End Functio
'-------------------------------------------------------------------------------

If this post helps click Yes

 

[kd]

Thank you so much Jacob, it works. You are the best!!!

Best Regards,

 

[Christine]

Jacob,

Your post is very helpful, but my conditional format changes the font to
red, not the cell backroung. Could you modify your below code so that the
helper column identifies those cells?

Thank you,

Chris