how to tell if an excel cell has conditional formatting applied using VBA

4
votes

I have a range of cells which have conditional formatting applied.

The aim was to visually separate the values with a positive, negative, and no change.

How can I use VBA to check if the cells have conditional formatting applied to them (such as color of the cell due to being a negative number)?

3
excelvbaconditional-formatting
pnuts if you can tell me how to test for the presence of a style in a cell (when it has more than one, say "comma" and "Good") then I will spend the time to go through all the ones I have not responded to. Is there a filter I can apply in my account to see all those questions? – yoshiserry

3 Answers

3
votes

See if the Count is zero or not:

Sub dural()
    MsgBox ActiveCell.FormatConditions.Count
End Sub
1
votes

To use VBA to check if a cell has conditional formatting use the following code

Dim check As Range
Dim condition As FormatCondition
Set check = ThisWorkbook.ActiveSheet.Cells.Range("A1") 'this is the cell I want to check
Set condition = check.FormatConditions(1) 'this will grab the first conditional format
condition. 'an autolist of methods and properties will pop up and 
           'you can see all the things you can check here
'insert the rest of your checking code here

You can use parts of this code above with a for each loop to check all the conditions within a particular range.

0
votes

you can use Range.DisplayFormat to check if the conditional formatting is applied. Put some data into column A and then use the below code example

Private Sub TestConditionalFormat()
Range("A:A").FormatConditions.AddUniqueValues
Range("A:A").FormatConditions(1).DupeUnique = xlDuplicate
Range("A:A").FormatConditions(1).Interior.Color = 13551615


Dim rCell       As Range
Dim i           As Long
For Each rCell In Range(Range("A1"), Range("A1").End(xlDown))
If rCell.DisplayFormat.Interior.Color = 13551615 Then
i = i + 1
End If
Next
MsgBox i & " :Cells Highlights for Duplicates."
End Sub