Hide columns with a specific color set by conditional formatting

[Wim]

Option Explicit

Sub HideColumns()
    Dim cell As Range
     For Each cell In Range("G2:T2")
 If cell.Interior.Color = RGB(255, 0, 0) Or cell.Interior.Color = RGB(204, 255, 204) Then
  Columns(cell.Column).EntireColumn.Hidden = True
  End If
  Next
End Sub

The above code works perfect when the color format of a cell is physically changed but if the color is determined by conditional formatting it does not work. I also tried to use ColorIndex but no difference. Is it possible to make it work?

 

[NARAYANK991]

Hi Wim ,

The problem is that neither the Color nor the ColorIndex property will return the conditionally formatted color of a cell.

For the code to detect the CF color , it requires the code to recognize which of the different CF rules has been satisfied , and then use the color associated with that rule. The code is quite complex , and is available on the net , and possibly on this forum.

The complexity of the code will depend on the number of CF rules present , and which one is actually responsible for the color.

Narayan

 

[Wim]

Hi Narayan,

Thank you for the response .... now to find that code ... google just returns the normal color formatting or want to teach you about conditional formatting ...

Take care
Wim

 

[NARAYANK991]

Hi Wim ,

I have the code , in the form of a function ; see if you can use this. This has been copied from here :

http://www.vbaexpress.com/kb/getarticle.php?kb_id=190

Function ConditionalColor(rg As Range, FormatType As String) As Long
    'Returns the color index (either font or interior) of the first cell in range rg. If no _
    conditional format conditions apply, Then returns the regular color of the cell. _
    FormatType Is either "Font" Or "Interior"
    Dim cel As Range
    Dim tmp As Variant
    Dim boo As Boolean
    Dim frmla As String, frmlaR1C1 As String, frmlaA1 As String
    Dim i As Long

    'Application.Volatile    'This statement required if Conditional Formatting for rg is determined by the _
    value of other cells

    Set cel = rg.Cells(1, 1)
    Select Case Left(LCase(FormatType), 1)
    Case "f" 'Font color
        ConditionalColor = cel.Font.Color
    Case Else 'Interior or highlight color
        ConditionalColor = cel.Interior.Color
    End Select

    If cel.FormatConditions.Count > 0 Then
        'On Error Resume Next
        With cel.FormatConditions
            For i = 1 To .Count 'Loop through the three possible format conditions for each cell
                frmla = .Item(i).Formula1
                If Left(frmla, 1) = "=" Then 'If "Formula Is", then evaluate if it is True
                    'Conditional Formatting is interpreted relative to the active cell. _
                    This cause the wrong results If the formula isn 't restated relative to the cell containing the _
                    Conditional Formatting--hence the workaround using ConvertFormula twice In a row. _
                    If the Function were Not called using a worksheet formula, you could just activate the cell instead.
                    frmlaR1C1 = Application.ConvertFormula(frmla, xlA1, xlR1C1, , cel)
                    frmlaA1 = Application.ConvertFormula(frmlaR1C1, xlR1C1, xlA1, xlAbsolute, ActiveCell)
                    boo = IIf(IsError(Application.Evaluate(frmlaA1)), False, Application.Evaluate(frmlaA1))
                Else 'If "Value Is", then identify the type of comparison operator and build comparison formula
                    Select Case .Item(i).Operator
                    Case xlEqual ' = x
                        frmla = cel & "=" & .Item(i).Formula1
                    Case xlNotEqual ' <> x
                        frmla = cel & "<>" & .Item(i).Formula1
                    Case xlBetween 'x <= cel <= y
                        frmla = "AND(" & .Item(i).Formula1 & "<=" & cel & "," & cel & "<=" & .Item(i).Formula2 & ")"
                    Case xlNotBetween 'x > cel or cel > y
                        frmla = "OR(" & .Item(i).Formula1 & ">" & cel & "," & cel & ">" & .Item(i).Formula2 & ")"
                    Case xlLess ' < x
                        frmla = cel & "<" & .Item(i).Formula1
                    Case xlLessEqual ' <= x
                        frmla = cel & "<=" & .Item(i).Formula1
                    Case xlGreater ' > x
                        frmla = cel & ">" & .Item(i).Formula1
                    Case xlGreaterEqual ' >= x
                        frmla = cel & ">=" & .Item(i).Formula1
                    End Select
                    boo = Application.Evaluate(frmla) 'Evaluate the "Value Is" comparison formula
                End If

                If boo Then 'If this Format Condition is satisfied
                    On Error Resume Next
                    Select Case Left(LCase(FormatType), 1)
                    Case "f" 'Font color
                        tmp = .Item(i).Font.Color
                    Case Else 'Interior or highlight color
                        tmp = .Item(i).Interior.Color
                    End Select
                    If Err = 0 Then ConditionalColor = tmp
                    Err.Clear
                    On Error GoTo 0
                    Exit For 'Since Format Condition is satisfied, exit the inner loop
                End If
            Next i
        End With
    End If

End Function

Narayan

 

[Wim]

Thanks Narayan ... will try my best still need to figure out how to execute a function ... Take care. Wim

 

[Wim]

Hi Narayan

Tried that. The code basically determines the color in Cell A1 when you enter =ConditionalColor(A1,"interior) somewhere in the work sheet, Unfortunately unlike what the name suggests, is still only pick up the color when it is manually formatted with a color. It does not pick up a "conditional formatted color".

I picked up the following elsewhere on the net but mus still figure out if it works:

"you can test each cell to see if it is conditionally formatted to some colour
vb Code:

For Each c In Range("d1:d4")
[LIST=1]
[*]    For Each f In c.FormatConditions
[*]        If f.Interior.ColorIndex = 6 Then 'do whatever
[*]    Next
[*]Next

[/LIST]