Get Displayed Cell Color (whether from Conditional Formatting or not)
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:pray:) 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:
ElseIf .Type = xlExpression Then
It seems that Excel uses the active cell to figure out relative cell references for a specified cell when returning the Formula1 property when a Conditional Format is using a formula rather than a cell value relation. This had me going for quite awhile as sometimes the code worked and other times it didn't. Finally, I noticed the discrepencies were related to what cell was active (the cell with the Conditional Format or some other cell). Because of that, it is necessary to activate the cell being examined before asking it for its formula. So, this need to activate the cell is what stops this function from being able to be used as a UDF.
*** NOTE: See Updated Code In My Reply To This Message ***
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837
https://www.eileenslounge.com/viewtopic.php?f=21&t=40701&p=314836#p314836
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314621#p314621
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314619#p314619
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314600#p314600
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314599#p314599
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314274#p314274
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314229#p314229
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
Have you found a fix for Office 2007?
