Filter by Colour - conditional formating

  • Thread starter Thread starter kd
  • Start date Start date
K

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
 
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
Click to expand...
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
 
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
 
Post your conditional formatting formula and the UDF formula which you tried
so as to recreate.

If this post helps click Yes
 
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
 
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
 
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
Click to expand...
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
 
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
 
Back
Top