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

 

[schielrn]

As much as I have reserached, I have never came across anything that can give you the color of something formatted by conditional formatting.

From experience you have to build logic into the code that is the same as your conditional formatting if you want to use that in your code.

Even though that is probably not what you wanted to hear.

 

[Mister H]

Hi schielrn:

THANKS

Definately NOT what I wanted to hear but at least now I know...

Would it be the same answer if the formatting put in a word instead of or as well as a color? Probably so, because it is still conditional formatting. Correct?

When I found the code I posted I thought I was onto something. It will definately take a lot longer to write all the conditional formatting into my VBA. I can just do a Fill Down

Oh well, back to the Drawing Board...

Take Care,
Mark

 

[jsmith4892002]

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.

 

[Rick Rothstein]

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 see this is an old thread, but for those who might trip across it, either now or in the future, perhaps the function (cannot be used as a UDF though) that I posted in my mini-blog article here would be of use...

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

Make sure to read Message #1 first as it sets the stage for the code, but then make sure to read Message #2 for a minor revision to that code. Also make sure to read the note appearing after the code block in Message #2.

 

[T. Valko]

Chip Pearson has info about this at his site:

http://www.cpearson.com/Excel/CFColors.htm

 

[jsmith4892002]

I loaded this up hoping it would return the correct values and it seems to be returning values for the standard pre-condition format. It does not change the values returned on cells that are different colors based on the conditional format expression. It looks like it should work. I am using color variations for both the background fill and foreground text in the conditional formats. I suspect this is related to the comment about more than one change causes it to fail.

Thanks for the help.

 

[mikerickson]

This worked in Excel 2004 (and 2003?) but not afterwards.

Sub convertSheet()
    Dim sheetToConvert As Worksheet
    Dim rngConditionalFormatted As Range
    Dim uiRange As Range
    
    On Error Resume Next
    Set uiRange = Application.InputBox("Select a Worksheet to convert", Type:=8)
    On Error GoTo 0
    If uiRange Is Nothing Then Exit Sub
    If MsgBox("Convert " & Trim(Left(Application.Substitute(uiRange.Address(, , , True), "$", String(255, " ")), 255)) & " ?", _
            vbYesNo) = vbNo Then Exit Sub
    Set sheetToConvert = uiRange.Parent
    'Set sheetToConvert = ThisWorkbook.Sheets("Sheet1"): Rem adjust
    
    On Error Resume Next
    Set rngConditionalFormatted = sheetToConvert.Cells.SpecialCells(xlCellTypeAllFormatConditions)
    On Error GoTo 0
    
    If Not rngConditionalFormatted Is Nothing Then
        Call ConvertCFtoBaseFormat(rngConditionalFormatted)
    Else
        MsgBox "no cf"
    End If
End Sub

Sub ConvertCFtoBaseFormat(rangeToConvert As Range)
    Dim oneCell As Range
    Application.ScreenUpdating = False
    For Each oneCell In rangeToConvert
        If CFormatMet(oneCell) <> 0 Then
            Call CopyCFto(oneCell, CFormatMet(oneCell))
        End If
        oneCell.FormatConditions.Delete
    Next oneCell
    Application.ScreenUpdating = True
End Sub

Sub CopyCFto(sourceCell As Range, CFConditionMet As Long, Optional destinationCell As Range)
    Rem Set destination cell base formatting = source cell conditional format #
    If destinationCell Is Nothing Then Set destinationCell = sourceCell.Cells(1, 1)
    Dim anEdge As Variant
    With sourceCell.Cells(1, 1).FormatConditions(CFConditionMet)
        With .Font
            If Not IsNull(.Bold) Then destinationCell.Font.Bold = .Bold
            If Not IsNull(.Italic) Then destinationCell.Font.Italic = .Italic
            If Not IsNull(.Underline) Then destinationCell.Font.Underline = .Underline
            If Not IsNull(.Strikethrough) Then destinationCell.Font.Strikethrough = .Strikethrough
            If Not IsNull(.ColorIndex) Then destinationCell.Font.ColorIndex = .ColorIndex
        End With
        
        On Error Resume Next
        For Each anEdge In Array(xlLeft, xlRight, xlTop, xlBottom)
            With .Borders(anEdge)
                If Not IsNull(.LineStyle) Then destinationCell.Borders(anEdge).LineStyle = .LineStyle
                If Not IsNull(.Weight) Then destinationCell.Borders(anEdge).Weight = .Weight
                If Not IsNull(.ColorIndex) Then destinationCell.Borders(anEdge).ColorIndex = .ColorIndex
            End With
        Next anEdge
        On Error GoTo 0
        
        With .Interior
            If Not IsNull(.PatternColorIndex) Then _
                    destinationCell.Interior.PatternColorIndex = .PatternColorIndex
            If Not IsNull(.Pattern) Then destinationCell.Interior.Pattern = .Pattern
            If Not IsNull(.ColorIndex) Then destinationCell.Interior.ColorIndex = .ColorIndex
        End With
    End With
End Sub

Function CFormatMet(oneCell As Range) As Long
    Rem which of the three conditional formatting conditions is met
    Rem given a cell, returns the number of the conditional format condtion that is met
    Rem if CF not engaged, returns 0
    Dim testFormula As String, tempFormula As String
    Dim i As Long
        With oneCell
            For i = 1 To .FormatConditions.Count
                testFormula = .Value
                If testFormula = vbNullString Then testFormula = 0
                With .FormatConditions(i)
                    If .Type = xlCellValue Then
                        Select Case .Operator
                            Case xlBetween
                                testFormula = "AND(" & .Formula1 & "<=" & testFormula & "," & testFormula & "<=" & .Formula2 & ")"
                            Case xlNotBetween
                                testFormula = "NOT(AND(" & .Formula1 & "<=" & testFormula & "," & testFormula & "<=" & .Formula2 & "))"
                            Case xlEqual
                                testFormula = testFormula & "=" & .Formula1
                            Case xlGreater
                                testFormula = testFormula & ">" & .Formula1
                            Case xlGreaterEqual
                                testFormula = testFormula & ">=" & .Formula1
                            Case xlLess
                                testFormula = testFormula & "<" & .Formula1
                            Case xlLessEqual
                                testFormula = testFormula & "<=" & .Formula1
                            Case xlNotEqual
                                testFormula = testFormula & "<>" & .Formula1
                        End Select
                    Else
                        tempFormula = Application.ConvertFormula(.Formula1, FromReferenceStyle:=xlA1, ToReferenceStyle:=xlR1C1)
                        testFormula = Application.ConvertFormula(tempFormula, FromReferenceStyle:=xlR1C1, ToReferenceStyle:=xlA1, _
                                                ToAbsolute:=True, RelativeTo:=oneCell)
                    End If
                End With
                If Evaluate(testFormula) Then Exit For
            Next i
        End With
        CFormatMet = i Mod (oneCell.FormatConditions.Count + 1)
End Function

 

[jsmith4892002]

Hi Chip, Thanks for the link. I loaded up all of the functions and each one with all options still gives no difference between a conditional formatted cell and one that does not meet the conditions. Of the requirements listed:

• You are calling ActiveCondtion from a worksheet cell,
  AND
• The cell passed to ActiveCondtion uses a "Formula Is"
  rather than
  "Cell Value Is" condition, AND
• The formula used in the condition formula contains relative
  addresses

I'm using "Cell value is", but the condition formula does use relative addresses. The code I used above for excel 2010 works fine, but the ActiveCell.DisplayFormat.Interior.Color does not work in 2007.

 

[shg]

It's a lot easier to put the relevant CF formula in a cell and use that for both CF and counting.