VBA Code to find Interior Color set by Conditional Formatting

[Mister H]

Hi All:

I have a spreadsheet where I have set up Conditional Formatting in some cells. IF certain criteria are not met then the cell turns red
.ColorIndex = 3

Is there any VBA Code that can detect interior color that was set up by Conditional Formatting. I have been seraching the internet for quite a while and found the code below which I modified slightly. It works if I set the interior color to RED via formatting but it ddoes not seem to recognize the RED interior when it is set up through COnditional Formatting. Can this code be modified to work for me? Is ther another code I could use? I would appreciate even if someone can tell me "Not Possible". THANKS.

Sub FindRedInterior()
Dim FoundCell As Range
 
'just in case there's other stuff that's been specified
Application.FindFormat.Clear
Application.FindFormat.Interior.ColorIndex = 3
 
With ActiveSheet
Set FoundCell = .Range("Revenue_Distribution").Find(What:="", _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=True)
If FoundCell Is Nothing Then
Exit Sub
 
Else

Application.Goto FoundCell, Scroll:=True
MsgBox prompt:="Please fill in any cells highlighted in RED " _
& "and then click on the Email Button again" _
& vbNewLine & "Start with: " _
& FoundCell.Address(0, 0), _
Title:="Jrnl 1 Corrections"
 
End If
End With
End Sub

ANY Suggestions

THANKS for looking,
Take Care,
Mark

 

[mikerickson]

Using the .DisplayFormat property in a UDF will cause it to return #VALUE when used in a worksheet formula, even though the UDF will work fine when called by VB. (Similarly the .Find and .SpecialCells properties and others).

Testing the conditions that tigger the CF would we the way to go.