Macro won't recognize conditional formatting

[Matt40Forte]

Hey Everyone,

I have a macro written in order to judge a cell based off its color. The code is below:

Function CheckColor1(range)
If range.Interior.Color = RGB(255, 0, 0) Then
CheckColor1 = "Stop"
ElseIf range.Interior.Color = RGB(0, 255, 0) Then
CheckColor1 = "Go"
Else
CheckColor1 = "Neither"
End If
End Function

This works for cells that I have filled manually, but if I have conditional formatting with the specified colors I just get "Neither".

Is there a way to modify this for it to work with conditional formatting fills?

 

[JoeMo]

If you are using Excel 2010 or a later version, try:

range.DisplayFormat.Interior.Color

which allows determination of the format properties of range regardless of how they were established (i.e. manually or by CF).

 

[DushiPunda]

Why not just use the same logic you are using for your conditional formatting rules? So, instead of looking at the color of the cells, you're doing the same logic? Does that make sense? Maybe this can help you out as well: VBA function to test if cell is conditionally formatted in Excel - Stack Overflow

 

[MARK858]

If you are using Excel 2010 or a later version, try:

range.DisplayFormat.Interior.Color

which allows determination of the format properties of range regardless of how they were established (i.e. manually or by CF).

Hi JoeMo, I am afraid DisplayFormat doesn't work with UDF's

Note that the DisplayFormat property does not work in user defined functions. For example, in a worksheet function that returns the interior color of a cell, you use a line similar to: Range.DisplayFormat.Interior.ColorIndex. When the worksheet function executes, it returns a #VALUE ! error

.


Range.DisplayFormat Property (Excel)

 

[JoeMo]

Hi JoeMo, I am afraid DisplayFormat doesn't work with UDF's

.


Range.DisplayFormat Property (Excel)

Thanks for the reminder Mark - I had forgotten that entirely!