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

 

[ricardo_matsumura]

Hi Mark, After three year and three months and 18 days, here is the aswer> It is quite simply:

Sub GetFomatColor()

Dim FormColor1 As Variant

Range("C11:c17").Select
FormColor1 = Selection.FormatConditions(1).BarColor.Color


End Sub

 

[ricardo_matsumura]

Hi Mark, after 3 year, 3 months and 18 days, here is the answer:

Sub GetFomatColor()

Dim FormColor1 As Variant

Range("C11:c17").Select
FormColor1 = Selection.FormatConditions(1).BarColor.Color


End Sub

 

[Mister H]

Lol THANKS for your help Better late then never. That little piece of code will come in handy. Thanks

 

[Hwulex]

Thank you for the code, Ricardo, I was searching long and hard before I found this. Unfortunately, however, this code will simply detect if conditional formatting exists across a range, and will return the colour that would be set whether or not it is actually applied (that is, conditionally true).

If anybody knows if it is possible to detect cell colour after conditional formatting is applied (ie false/blank if condition isn't met), it's that in which I'm really interested.

 

[Rick Rothstein]

If anybody knows if it is possible to detect cell colour after conditional formatting is applied (ie false/blank if condition isn't met), it's that in which I'm really interested.

With limitations, see Message #5.

 

[Hwulex]

With limitations, see Message #5.

Thanks, Rick. Not sure how I missed that first time around, but it works as described. It operates on a single-cell basis, but I could iterate over a range, check, and break if colour found.

I did actually, since posting, also find this solution via Stack Exchange which works for me across a range of cells. It doesn't so much find the colour regardless of how it is set, rather tests the condition of the cell against its value and returns the colour being set if true. My cell colours are only ever set by CF, so this solution is fine for me.

BET: Microsoft Excel Conditional Formatting - Sorting By Conditional Formatting


I am assessing both currently, but good to know it is possible. I have up to 28 sheets, with up to 1000 rows per sheet so don't want this to get too sluggish if I'm checking where the first colour is set per-sheet.

 

[ayeji]

HI. I am trying to write a code for getting the conditional formatting color of a RANGE instead of a cell as in your code. Also, I can not run for each cell in range, because the evaluating condition is an expression mentioned below. It always picks up cell D4, so always the color of D4 is returned.Cond 1: Red if =VLOOKUP(D4,'SHEETA'!$D$14:$F$48,3,FALSE) = "CONDITION A"Cond 2: Green if =VLOOKUP(D4,'SHEETA'!$D$14:$F$48,3,FALSE) = "CONDITION B"Else, No colorAny help is appreciated.!Thanks

 

[Hwulex]

For anybody finding this thread through search there is a solution to your problem. I found on page 2 of Rick's link mention of a new VBA Property introduced in Excel 2010 that will return the true current colour of a cell, even if set by conditional formatting. This means you do not need to evaluate the FormatConditions, you can just query the true colour directly.

Forum post: Get Displayed Cell Color (whether from Conditional Formatting or not) - Page 2
MSDN link: Range.DisplayFormat Property (Excel)

Despite what MSDN says, I can confirm it works perfectly in a UDF as per this example:
Excel 2010 Function using DisplayFormat doesn't work as a worksheet function (Application-Defined / Object-Defined error)


This could probably be a lot cleaner, but for now it's a working prototype.

Public Function ChangeTabColorBasedOnValidationColorInSheet(intSheet As Integer)    ' Var declarations
    Dim X, Y As Integer
    Dim Z As Long


    ' If there aren't any conditions, don't bother going any further
    If Worksheets(intSheet).UsedRange.FormatConditions.Count < 1 Then Exit Function


    ' Step through each cell in the sheet and determine if conditional colours and change tab-color to match
    With Worksheets(intSheet)
        For X = 2 To .UsedRange.Rows.Count
            For Y = 1 To .UsedRange.Columns.Count
                Z = getCellColorForReals(Range(Cells(X, Y), Cells(X, Y)))
    
                Select Case Z
                    Case 16777215
                    Case xlColorIndexNone
                    Case clrGreen ' Green
                    Case clrWhite ' White
                    Case Else
                        Exit For
                End Select
            Next
    
            Select Case Z
                Case 16777215
                Case xlColorIndexNone
                Case clrGreen ' Green
                Case clrWhite ' White
                Case Else
                    'MsgBox "Row: " & X & ", Col: " & Y & ", Clr: " & Z & ", Val: " & .Range(Cells(X, Y), Cells(X, Y)).Value
                    .Tab.Color = Z
                    Exit For
            End Select
        Next
    
    End With
End Function




Function getCellColorForReals(r As Range) As Long
    getCellColorForReals = r.DisplayFormat.Interior.Color
End Function

 

[figment222]

Hi. Just found this thread and am getting to the point where I have more to contribute with Excel expertice. I stumbled on this code that works for Excel 2010, but NOT Excel 2007...hence my search. The following code will check the conditional background color of a cell.

If ActiveCell.DisplayFormat.Interior.Color = "16777215" Or ActiveCell.DisplayFormat.Interior.Color = "65280" Then
SendKeys "%{DOWN}"
End If

If you find what works for Excel 2007, please post it.

.DISPLAYFORMAT worked like a charm! Thanks! I was building a user form with labels that needed the formatting to match cell contents that were conditionally formatted. I thought I hit a major roadblock until I found this post. Thanks again!

 

[rodgeraj]

I've been searching for most of the morning for an updated formula and I cannot find anything that does give me a #VALUE! error Or only gives me the default (Non-conditional formatted) color.

Anyone have a current reference for this?