PDA

View Full Version : Solved: Detecting Conditional Formatting



Opv
07-22-2010, 09:52 AM
Is there a way to test for the background color of a particular cell if that color has been changed based on conditional formatting?

Artik
08-11-2010, 05:45 AM
Test this:Sub TestActiveCell()
Dim i As Byte
Dim FCond As FormatConditions

If ActiveCell.FormatConditions.Count = 0 Then
MsgBox "No conditional formatting."
Exit Sub
End If

ActiveSheet.UsedRange

Set FCond = ActiveCell.FormatConditions

'An empty cell in the active worksheet
With Range("AO1")
For i = 1 To FCond.Count
.FormulaLocal = FCond(i).Formula1
If .Value <> False Then
MsgBox "Condition No. " & i & " is satisfied" & vbCr & vbCr & _
"Visible in the worksheet cells fill color is (ColorIndex) " & _
FCond(i).Interior.ColorIndex

'When they met two or three conditions,
'the worksheet is displayed the color of a first fulfilling,
'so it makes no sense to check further.
Exit For
End If
Next i
.ClearContents
End With

Set FCond = Nothing

ActiveSheet.UsedRange
End Sub

Original code is here (sorry, in Polish :)) http://www.excelforum.pl/viewtopic.php?p=64617

Artik

Opv
08-11-2010, 08:05 AM
That works like a charm. Thanks!

Opv

Artik
08-12-2010, 02:18 PM
Because in US version of Excel Formula = FormulaLocal (for example in Polish version of Excel Formula <> FormulaLocal ), you can still try this (without the use of additional cell):Sub TestActiveCell_1()
Dim i As Byte
Dim FCond As FormatConditions

If ActiveCell.FormatConditions.Count = 0 Then
MsgBox "No conditional formatting."
Exit Sub
End If


Set FCond = ActiveCell.FormatConditions

For i = 1 To FCond.Count
If Application.Evaluate(FCond(i).Formula1) <> False Then
MsgBox "Condition No. " & i & " is satisfied" & vbCr & vbCr & _
"Visible in the worksheet cells fill color (ColorIndex) is " & _
FCond(i).Interior.ColorIndex

Exit For
End If
Next i


Set FCond = Nothing

End Sub In a more complex formula in conditional formatting, you probably may need a different formulation of the condition:If Application.Evaluate(FCond(i).Formula1) <> False Then
Artik

P.S.
I apologize for my language. I use a translator.