| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter by Colour - conditional formating
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Filter by Colour - conditional formating
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 Function '------------------------------------------------------------------------------- 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 --------------- Jacob Skaria "kd" wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Filter by Colour - conditional formating
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" wrote: 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 Function '------------------------------------------------------------------------------- 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 --------------- Jacob Skaria "kd" wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Filter by Colour - conditional formating
Post your conditional formatting formula and the UDF formula which you tried
so as to recreate. If this post helps click Yes --------------- Jacob Skaria "kd" wrote: 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" wrote: 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 Function '------------------------------------------------------------------------------- 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 --------------- Jacob Skaria "kd" wrote: 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Filter by Colour - conditional formating
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 "Jacob Skaria" wrote: Post your conditional formatting formula and the UDF formula which you tried so as to recreate. If this post helps click Yes --------------- Jacob Skaria "kd" wrote: 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" wrote: 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 Function '------------------------------------------------------------------------------- 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 --------------- Jacob Skaria "kd" wrote: 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Filter by Colour - conditional formating
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 Function '------------------------------------------------------------------------------- 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 Function '------------------------------------------------------------------------------- If this post helps click Yes --------------- Jacob Skaria "kd" wrote: 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 "Jacob Skaria" wrote: Post your conditional formatting formula and the UDF formula which you tried so as to recreate. If this post helps click Yes --------------- Jacob Skaria "kd" wrote: 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" wrote: 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 Function '------------------------------------------------------------------------------- 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 --------------- Jacob Skaria "kd" wrote: 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Filter by Colour - conditional formating
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 kd wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional Formating with colour | Excel Programming | |||
| conditional formating If A1 or B1 is blank then C1 (Font Colour Ch | Excel Worksheet Functions | |||
| copy rows based on cell colour by conditional formating | Excel Programming | |||
| Conditional Formating - Different row color also when using filter | Excel Discussion (Misc queries) | |||
| Excel - conditional formating on graphs. Change colour on a value | Charts and Charting in Excel | |||
Hybrid Mode
