VBA Express : Excel - Get Cell Color Function

Excel

Get Cell Color Function

Ease of Use

Easy

Version tested with

97, 2000, 2003

Submitted by:

byundt

Description:

Function returns the active cell interior or font color index, regardless of whether it was set by regular or Conditional Formatting.

Discussion:

People sometimes want to know what color a cell is. The answer is complicated if conditional formatting is used because you must first evaluate the conditional formatting criteria to see if any of them govern cell color. For users of Excel 95 and other spreadsheet applications that don't support conditional formatting, you can use this information to convert Conditional Formatting to regular formatting with the same colors.

Code:

instructions for use

			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 
 
 
Sub NonConditionalFormatting() 
    Dim cel As Range 
    Application.ScreenUpdating = False 
     
     'Remove conditional formatting from entire worksheet
     'For Each cel In ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllFormatConditions)
    For Each cel In Selection 'Remove conditional formatting from selected cells
        If cel.FormatConditions.Count > 0 Then 
            cel.Interior.ColorIndex = ConditionalColor(cel, "Interior") 'Replace the interior (highlight) color
            cel.Font.ColorIndex = ConditionalColor(cel, "Font") 'Replace the font color
            cel.FormatConditions.Delete 'Delete all the Format Conditions for this cell
        End If 
    Next cel 
     
    Application.ScreenUpdating = True 
End Sub

How to use:

Copy the code above.
Hit ALT + F11 to open the VBA Editor (VBE).
Choose your workbook at left and hit Insert-Module from the menu.
Paste the code into the code window that appears at right.
Hit the Save diskette and close the VBE.

Test the code:

To test the function, apply Conditional Formatting to a cell, then enter a worksheet formula like:
=ConditionalColor(A1,"interior") or =ConditionalColor(A1,"font")
The first formula returns the color index number for the highlight color in cell A1, while the second returns the font color index number.
Note that the function will update only when cell A1 changes its value--which may not coincide with color changes if the Conditional Formatting is based on the value of another cell. If so, uncomment the Application.Volatile statement in the function.
Note too, that the function returns incorrect results if the Conditional Formatting is based on a formula and the sheet being interrogated is not the active sheet. To get around this difficulty, activate the sheet before calling the function.
The NonConditionalFormatting sub wipes out all Conditional Formatting from the selected cells, replacing it with regular formatting using the same colors. To use the sub, select the a range of cells that need their Conditional Formatting converted to regular formatting
ALT + F8 to open the Macro selector, select the NonConditionalFormatting macro and click the Run button
Save the file with a different file name
Note that all Conditional Formatting in the selected range will get wiped out, including bold or underline styles, borders and patterns.
Note too, if you want all the Conditional Formatted cells on the worksheet changed at once, there is a line in the macro that has been commented out for this purpose. Delete the apostrophe in front of this line and add one in front of the line beginning "For Each cel In Selection"

Sample File:

ConditionalFormattingToRegularFormatting.zip 14.01KB

Approved by mdmackillop

This entry has been viewed 352 times.