VBA: determining current conditional format for a cell
Posted by Tim Francis-Wright on October 06, 2000 2:43 PM
Is there a way to determine the conditional
format that is currently applying to a cell?
Right now, I have a function that checks the
interior color of a cell (checking if it's red,
for example, by evaluating [...].interior.colorindex = 3,
but if the cell has a conditional format to change
the cell interior to red, I'd like to know the
actual color of the cell rather than the default
color (what Excel is currently giving me).
Posted by Ivan Moala on October 06, 2000 4:25 PM
Tim, not sure about getting this BUT.....
the following will evaluate the formula part
and match against the actual cell result ie. test
the formula part of the CF against the Cell and
give the result as a color index
Sub test()
Dim x As Integer
Dim C As Integer
Dim Fm
Dim D
Range("A1").Select
For x = 1 To ActiveCell.FormatConditions.Count
'Note:Uses Formula2 which returns the value or expression associated
'with the second part of a conditional format or
'data validation. Used only when the data validation
'conditional format Operator property is xlBetween or xlNotBetween.
'Can be a constant value, a string value, a cell reference,
'or a formula. Read-only String.
D = ActiveCell.FormatConditions.Item(x).Formula1 'OR Formula2 ?? see note
If D = ActiveCell.Text Then
C = ActiveCell.FormatConditions(x).Interior.ColorIndex
End If
Next
If IsEmpty(C) Then C = ActiveCell.Interior.ColorIndex
End Sub
Posted by Tim Francis-Wright on October 10, 2000 11:16 AM
I thought that I had this working--I used
D = Evaluate(ActiveCell.FormatConditions.Item(x).Formula1)
and then checked if D=True, but then I ran into
another problem. If I used a cell other than
ActiveCell in the sub, VBA looks at the
formula1 in a relative fashion. Grumble. Such a pain.
I'm going to redefine my conditional formats
so they look at a different column on the
worksheet, then do some magic on the new column.
