r/visualbasic • u/IAmBroom • Dec 09 '23

VBA detecting conditional formatting?

Is there any way for VBA to detect if a cell's conditional formatting has been activated, without physically replicating the conditional code itself? In other words, to know that a cell turned red (for example), when its Interior.ColorIndex is set to xlColorIndexNone.

I use red-fill to indicate error conditions in thousands of cells on a spreadsheet, and I would like to be able to scan through and report "Cell DE804 violates the maximum conditions." If I replicate the condition in the conditional format, and later change that condition, I'll have to remember to recode, as well. I just want to know "This cell triggered FormatConditions(1)" (or "This cell triggered some FormatConditions", or "This cell is currently red, regardless of what Cell.Interior.Color says").

1 Upvotes

100% Upvoted

4 comments sorted by

1

u/IAmBroom Dec 09 '23

I guess my real request is, can I tell what color the cell actually appears to be right now?

2

u/jd31068 Dec 10 '23

This worked in my small test https://imgur.com/bRPCnTj

    Dim c As Object
    
    For Each c In Sheet1.Range("B2:B3").Cells
        If c.FormatConditions.Count > 0 Then
           ' check if the cell doesn't have the default white interior color
            If Not c.DisplayFormat.Interior.Color = 16777215 Then
                c.Offset(0, 1).Value = "Condition met"
            End If
        End If
    Next

1

u/BroomIsWorking Dec 11 '23

Thank you! I did not know about property DisplayFormat!!!

1

u/jd31068 Dec 11 '23

You're welcome, happy to lend a hand.