Solved: Check conditional format colour?

[Simon Lloyd]

Hi all, i am trying to Msgbox the colorindex of a cell that is conditionally formatted, the problem is that the colour doesn't really exist in the cell in the same sense as when you change pattern colour, i'm using the CellIs criteria and have tried something like If Target.FormatCondition(1).Interior.ColorIndex=3 Then...but of course its not that easy and it doesnt work!

I am working with a single column and want to check if the cell is conditionally formatted Red after an entry is made or cell is updated.

Any ideas?

[Bob Phillips]

It's a;; explained at http://www.xldynamic.com/source/xld.CFConditions.html

[Simon Lloyd]

Yes as i see Bob, naturally your well penned solution works well but i can only get it to check if the condition is met, there are two conditions for CellIs one shows Green one Red the cell is never xlNone, so consequently the function always shows the condition is met how can it be adapted to show if the condition is met and the format applied is colorindex 3? in the one column there are 2 different conditional formats that would produce red but not for the same cell i.e A1 may be CellIs =< B1 and produce red, A4 may be CellIs >= B4 and produce red. I guess its probably best that i produce the CF using Case Is for the range and check my criteria that way, i just thought that it would be easier to check the format of the cell....but then again it's MS!

[Bob Phillips]

You would need to add an extra check that the formatcondition colorindex is 3. Should be straight-forward.

[Simon Lloyd]

Yes of course how dim of me! it must be the heat!

[Bob Phillips]

Took me half a minute

User like so, =IsCFMet(D1,3)




'---------------------------------------------------------------------
Public Function IsCFMet(rng As Range, Optional CI As Variant) As Boolean
'---------------------------------------------------------------------
Dim oFC As FormatCondition
Dim sF1 As String
Dim iRow As Long
Dim iColumn As Long

Set rng = rng(1, 1)
If rng.FormatConditions.Count > 0 Then
For Each oFC In rng.FormatConditions
If oFC.Type = xlCellValue Then
Select Case oFC.Operator
Case xlEqual
IsCFMet = rng.Value = oFC.Formula1
Case xlNotEqual
IsCFMet = rng.Value <> oFC.Formula1
Case xlGreater
IsCFMet = rng.Value > oFC.Formula1
Case xlGreaterEqual
IsCFMet = rng.Value >= oFC.Formula1
Case xlLess
IsCFMet = rng.Value < oFC.Formula1
Case xlLessEqual
IsCFMet = rng.Value <= oFC.Formula1
IsCFMet = (rng.Value >= oFC.Formula1 And _
rng.Value <= oFC.Formula2)
Case xlNotBetween
IsCFMet = (rng.Value < oFC.Formula1 Or _
rng.Value > oFC.Formula2)
End Select
Else
're-adjust the formula back to the formula that applies
'to the cell as relative formulae adjust to the activecell
With Application
iRow = rng.Row
iColumn = rng.Column
sF1 = .Substitute(oFC.Formula1, "ROW()", iRow)
sF1 = .Substitute(sF1, "COLUMN()", iColumn)
sF1 = .ConvertFormula(sF1, xlA1, xlR1C1)
sF1 = .ConvertFormula(sF1, xlR1C1, xlA1, , rng)
End With
IsCFMet = rng.Parent.Evaluate(sF1)
End If
If IsCFMet Then

If Not IsMissing(CI) Then

If oFC.Interior.ColorIndex <> CI Then IsCFMet = False

End If
Exit Function
End If
Next oFC
End If 'rng.FormatConditions.Count > 0

End Function

[Simon Lloyd]

Bob, thanks for that, but of course you know i went the lazy way and used a Case statement...etc, i have to say i did then try your solution and liked it!....i much prefer to use Excels own built in functions these days other than when i first started out in my dictatorship with the use of VBA, it's so much smoother or "seamless" when doing so.

[Bob Phillips]

You know I am a great proponent of using the built-in functionality (I would argue that is the lazy way).

[Simon Lloyd]

You know I am a great proponent of using the built-in functionality (I would argue that is the lazy way).Bob i would agree with you on the latter but just looking at the code you devised to combat excels inability to simply check the psuedo status of cell i would say was a little labour intensive compared to the built in automated functions and checks that excel is very capable of!

As for the former.....well thats where you pointed me in to following your lead some time ago with worksheet functions!