Option Explicit
Sub CFColorIndex()
Range("A2:A18").Select
Selection.Interior.ColorIndex = 40
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="15", Formula2:="16"
Selection.FormatConditions(1).Interior.ColorIndex = 34
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(A2:A18;9)<4"
Selection.FormatConditions(2).Interior.ColorIndex = 33
Range("C20").Select
Dim Rng As Range, Clls As Range
Dim iJ As Integer
For iJ = 2 To 18
With Cells(iJ, 1)
.Offset(, 1) = .Interior.ColorIndex
14 .Offset(, 2) = CheckConditionalFormat(Cells(iJ, 1))
End With
Next iJ
End Sub
' * * * * * *'
Function CheckConditionalFormat(MRng As Range) As Integer
'from http://www.mrexcel.com/archive/VBA/10959.html'
Dim bcheck As Integer 'Boolean'
Dim iW As Long
' Cells.FormatConditions.Item(1)?'
With MRng
For iW = 1 To .FormatConditions.Count
If .FormatConditions.Item(iW).Type = 1 Then
bcheck = 0 'False'
Select Case .FormatConditions.Item(iW).Operator
Case 1 ' between'
If .Value >= .FormatConditions.Item(iW).Formula1 And _
.Value <= .FormatConditions.Item(iW).Formula2 Then _
bcheck = 1 'True'
Case 2 ' not between'
If .Value < .FormatConditions.Item(iW).Formula1 Or _
.Value > .FormatConditions.Item(iW).Formula2 Then _
bcheck = 1 'True'
Case 3 ' equal to'
If .Value = .FormatConditions.Item(iW).Formula1 Then _
bcheck = 1 'True'
Case 4 ' not equal to'
If .Value <> .FormatConditions.Item(iW).Formula1 Then _
bcheck = 1 'True'
Case 5 ' greater then'
If .Value > .FormatConditions.Item(iW).Formula1 Then _
bcheck = 1 'True'
Case 6 ' less then'
If .Value < .FormatConditions.Item(iW).Formula1 Then _
bcheck = 1 'True'
Case 7 ' greater & equal then'
If .Value >= .FormatConditions.Item(iW).Formula1 Then _
bcheck = 1 'True'
Case 8 ' less & equal then'
If .Value <= .FormatConditions.Item(iW).Formula1 Then _
bcheck = 1 'True'
End Select
If bcheck = 1 Then 'True'
CheckConditionalFormat = .FormatConditions(iW).Interior.ColorIndex
Exit Function
End If
ElseIf .FormatConditions.Item(iW).Type = 2 Then
CheckConditionalFormat = .FormatConditions(iW).Interior.ColorIndex
Exit Function 'Type =2?'
ElseIf .FormatConditions.Item(iW).Type = 0 Then '?!!?'
CheckConditionalFormat = .Interior.ColorIndex
Exit Function
End If
Next iW
End With
CheckConditionalFormat = 0
End Function