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

**Rick Rothstein** · 03-23-2012, 05:40 AM

How can I tell what color the cell is? This question seems to come up quite often in the various forums and newsgroups that I visit. Almost universally, the person posing the question gets asked "Is the cell's color due to Conditional Formatting or not?" The reason? VB does does not provide direct access to the color displayed in a cell. So, I decide to try my hand at writing code to return the displayed color no matter how that color got into the cell. The following code (which I believe works correctly

) is the result of that effort...

SEE CODE UPDATE IN NEXT MESSAGE

Code:

' Arguments
' ----------------
' Cell - Required Range, not a String value, for a **single** cell
'
' CellInterior - Optional Boolean (Default = True)
'                True makes function return cell's Interior Color or ColorIndex based on
'                the ReturnColorIndex argument False makes function return Font's Color or
'                ColorIndex based on the ReturnColorIndex argument
'
' ReturnColorIndex - Optional Boolean (Default = True)
'                    True makes function return the ColorIndex for the cell property determined
'                    by the CellInterior argument False make function return the Color for the
'                    cell property determined by the CellInterior argument
'
Function DisplayedColor(Cell As Range, Optional CellInterior As Boolean = True, _
                        Optional ReturnColorIndex As Long = True) As Long
  Dim X As Long, Test As Boolean, CurrentCell As String
  If Cell.Count > 1 Then Exit Function
  CurrentCell = ActiveCell.Address
  For X = 1 To Cell.FormatConditions.Count
    With Cell.FormatConditions(X)
      If .Type = xlCellValue Then
        Select Case .Operator
          Case xlBetween:      Test = Cell.Value >= Evaluate(.Formula1) And Cell.Value <= Evaluate(.Formula2)
          Case xlEqual:        Test = Evaluate(.Formula1) = Cell.Value
          Case xlGreater:      Test = Cell.Value > Evaluate(.Formula1)
          Case xlGreaterEqual: Test = Cell.Value >= Evaluate(.Formula1)
          Case xlLess:         Test = Cell.Value < Evaluate(.Formula1)
          Case xlLessEqual:    Test = Cell.Value <= Evaluate(.Formula1)
          Case xlNotBetween:   Test = Cell.Value <= Evaluate(.Formula1) Or Cell.Value >= Evaluate(.Formula2)
          Case xlNotEqual:     Test = Evaluate(.Formula1) <> Cell.Value
        End Select
      ElseIf .Type = xlExpression Then
        Application.ScreenUpdating = False
        Cell.Select
        Test = Evaluate(.Formula1)
        Range(CurrentCell).Select
        Application.ScreenUpdating = True
      End If
      If Test Then
        If CellInterior Then
          DisplayedColor = IIf(ReturnColorIndex, .Interior.ColorIndex, .Interior.Color)
        Else
          DisplayedColor = IIf(ReturnColorIndex, .Font.ColorIndex, .Font.Color)
        End If
        Exit Function
      End If
    End With
  Next
  If CellInterior Then
    DisplayedColor = IIf(ReturnColorIndex, Cell.Interior.ColorIndex, Cell.Interior.Color)
  Else
    DisplayedColor = IIf(ReturnColorIndex, Cell.Font.ColorIndex, Cell.Font.Color)
  End If
End Function

Since I was the writing the code, I could not resist adding some extra functionality. I did this by including two optional arguments. The first, CellInterior, is a Boolean which controls whether the returned value is the color of the cell's interior or the cell's font. The default value is True which means the function returns the color of the cell's interior by default. The second, ReturnColorIndex, is also a Boolean and it controls whether the returned value is the ColorIndex or the RGB Color value. The default value is True which means the function returns the ColorIndex by default.

Unfortunately, this function cannot be used as a UDF (user defined function). The reason behind this will also help you understand why the code in the block underneath this statement looks the way it does...

Code: