How to get the Font Color, Fill Color of cells with Conditional Formatting for more than 3 criterias using VBA?

[all4excel]

How to get the Font Color, Fill Color of cells with Conditional Formatting for more than 3 criterias using VBA?

Hello Board,

Is it possible to get the Color Codes like Color Nos for cells which have conditional formatting..
When we try using the .Font.Color or .Font.ColorIndex it gives the number only for the color which is applied manually and not for any color via conditional formatting.

If i have more than three criterions in my conditional format, then is it possible to get the Font Color, Fill Color i.e. Interior Color and if possible other aspects such as Font.Italics or Font.Bold too using VBA?

Will the code be able to read the Font Color Code and Fill Color Code no from individual cell with conditional formatting rather than looking at a Range, In case, if we were to provide a cell range for example A2 where the Font Color is red and the Fill Color is Yellow and in cell A3 Font Color is Blue and the Fill Color is Orange then is it possible to just to give the Range as A2 or A3 and get both the details..as well as if the other aspects such as the Font Bold and Italics.

 

[Mohammad Basem]

Try this. Though it does not cover everything but it might help,

[FONT="Consolas"][SIZE="2"][COLOR="Navy"]Option Explicit

Sub ConditionalFormatProperties()

    Dim iConditions As Integer, iCount As Integer
    Dim sFontColor As String, sCellColor As String
    Dim sBold As String, sItalic As String
    Dim sText As String

    iConditions = Selection.FormatConditions.Count
    If iConditions = 0 Then Exit Sub

    On Error GoTo ErrHandler
    sText = "Condition" & vbTab & " Font " & vbTab & " Cell " & vbTab & _
        "Font" & vbTab & "Font" & vbNewLine
    sText = sText & "     No.  " & vbTab & vbTab & "Colour" & vbTab & _
        "Colour" & vbTab & "Bold" & vbTab & "Italic" & vbNewLine
    sText = sText & "---------" & vbTab & vbTab & "------" & vbTab & _
        "------" & vbTab & "------" & vbTab & "------"
    For iCount = 1 To iConditions
        With Selection.FormatConditions(iCount)
            sBold = IIf(.Font.Bold, "Yes", "No")
            sItalic = IIf(.Font.Italic, "Yes", "No")
            sFontColor = IIf(IsNull(.Font.ColorIndex), "Auto", .Font.ColorIndex)
            sCellColor = IIf(IsNull(.Interior.ColorIndex), "Auto", .Interior.ColorIndex)
            sText = sText & vbNewLine & iCount & vbTab & vbTab & sFontColor & _
                vbTab & sCellColor & vbTab & sBold & vbTab & sItalic
        End With
    Next
    MsgBox sText
    On Error GoTo 0
    Exit Sub

ErrHandler:
    On Error GoTo 0
    MsgBox "Inconsistent selection made. Please select one cell or range of cells " & _
        "having consistent conditional formatting", vbInformation, _
        "Inconsistent Conditional Formatting"

End Sub[/COLOR][/SIZE][/FONT]

 

[Rick Rothstein]

You may find the function I posted in this mini-blog article useful...

Get Displayed Cell Color (whether from Conditional Formatting or not)

 

[all4excel]

Hi Mohammed , Thanks for the code..

Please tell me how do I get write the syntax to get the numeric values for Font and Interior Colors and True or False for the Bold and Italic property..

@ Rick, thanks for the link.

I have applied the code and it works but it gives me the numeric value of only the first cell in the entire column which has conditional formatting applied to it..
Like I have 4 different conditions in the 4 cells of a column which have different FOnt Colors and DIfferent Interior colors as well as Italics and Bold but it jsut shows the numeric value for the first cell in the range of the conditional formatting column..

So please advise..

Thanks in advance

 

[Mohammad Basem]

Please tell me how do I get write the syntax to get the numeric values for Font and Interior Colors and True or False for the Bold and Italic property..

I am sorry, I did not get you well. Are you referring to something not shown in the code?

 

[all4excel]

Hi Mohammed,

I meant how do I get the numeric values for the Color Codes for Font and Interior as well as True & False for Bold and Italics?
I need to know the numbers or is it possible to directly get the same color pasted in a new cell in another sheet but just the color and not the conditional formatting

all4excel

 

[Mohammad Basem]

Hi,

To obtain the displayed format properties

[FONT="Consolas"][SIZE="2"][COLOR="Navy"]With Selection
    CellColor = .Interior.ColorIndex        [COLOR="Green"]'* Returns number or Null[/COLOR]
    With .Font
        FontColor = .ColorIndex             [COLOR="Green"]'* Returns number or Null[/COLOR]
        FontBold = .Bold                    [COLOR="Green"]'* Returns True or False[/COLOR]
        FontItalic = .Italic                [COLOR="Green"]'* Returns True or False[/COLOR]
    End With
End With[/COLOR][/SIZE][/FONT]

To obtain the conditional format properties

[FONT="Consolas"][SIZE="2"][COLOR="Navy"]iRules = Selection.FormatConditions.Count   [COLOR="Green"]'* No. of rules[/COLOR]
For iCount = 1 To iRules
    With Selection.FormatConditions(iCount)
        CellColor = .Interior.ColorIndex    [COLOR="Green"]'* Returns number or Null[/COLOR]
        With .Font
            FontBold = .Bold                [COLOR="Green"]'* Returns True or False[/COLOR]
            FontItalic = .Italic            [COLOR="Green"]'* Returns True or False[/COLOR]
            FontColor = .ColorIndex         [COLOR="Green"]'* Returns number or Null[/COLOR]
        End With
    End With
Next[/COLOR][/SIZE][/FONT]

Is this what you have asked for?

 

[Norie]

Just curious, but why do you want to find out the colours etc?

 

[all4excel]

I found this code somewhere but I need to also have something for Bold and Italics, can someone please help add something to this please

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.ColorIndex
    Case Else 'Interior or highlight color
        ConditionalColor = cel.Interior.ColorIndex
    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, , ActiveCell)
                    frmlaA1 = Application.ConvertFormula(frmlaR1C1, xlR1C1, xlA1, xlAbsolute, cel)
                    boo = 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.ColorIndex
                    Case Else 'Interior or highlight color
                        tmp = .Item(i).Interior.ColorIndex
                    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

 

[all4excel]

Hi,

To obtain the displayed format properties

[FONT=Consolas][SIZE=2][COLOR=Navy]With Selection
    CellColor = .Interior.ColorIndex        [COLOR=Green]'* Returns number or Null[/COLOR]
    With .Font
        FontColor = .ColorIndex             [COLOR=Green]'* Returns number or Null[/COLOR]
        FontBold = .Bold                    [COLOR=Green]'* Returns True or False[/COLOR]
        FontItalic = .Italic                [COLOR=Green]'* Returns True or False[/COLOR]
    End With
End With[/COLOR][/SIZE][/FONT]

To obtain the conditional format properties

[FONT=Consolas][SIZE=2][COLOR=Navy]iRules = Selection.FormatConditions.Count   [COLOR=Green]'* No. of rules[/COLOR]
For iCount = 1 To iRules
    With Selection.FormatConditions(iCount)
        CellColor = .Interior.ColorIndex    [COLOR=Green]'* Returns number or Null[/COLOR]
        With .Font
            FontBold = .Bold                [COLOR=Green]'* Returns True or False[/COLOR]
            FontItalic = .Italic            [COLOR=Green]'* Returns True or False[/COLOR]
            FontColor = .ColorIndex         [COLOR=Green]'* Returns number or Null[/COLOR]
        End With
    End With
Next[/COLOR][/SIZE][/FONT]

Is this what you have asked for?

Yes but if also you could include with the Interior Color or the Fill Color in the latter..and also any remaining aspect of conditional formatting I might have missed..

But If I am trying to apply the values of the colors from one SHeet to another how do I write the syntax..

Thanks Mohammed in Advance