Thanks again Bob.

I played around. Seems like the macro breaks when I add the If(AND...
conditions to the conditional formating.

When I set the conditional formating to:

=$D5<$G5 make cell d5 BLUE

=$D5$G5 make cell d5 GREEN

=$D5=0 make cell d5 BLACK

The macro works fine. However, if the value of the cell is 0, the cell is
colored Blue vs Black.

Not sure what to do.



"Bob Phillips" wrote:

Works fine for me fine.

Did you put CFColorindex in a standard code module (InsertModule), not a
sheet module, and not ThisWorkbook?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"carl" wrote in message
...
Trying this again.

I am using this Public Function:

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
CFColorindex = rng.Value = oFC.Formula1
Case xlNotEqual
CFColorindex = rng.Value < oFC.Formula1
Case xlGreater
CFColorindex = rng.Value oFC.Formula1
Case xlGreaterEqual
CFColorindex = rng.Value = oFC.Formula1
Case xlLess
CFColorindex = rng.Value < oFC.Formula1
Case xlLessEqual
CFColorindex = rng.Value <= oFC.Formula1
Case xlBetween
CFColorindex = (rng.Value = oFC.Formula1 And _
rng.Value <= oFC.Formula2)
Case xlNotBetween
CFColorindex = (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
CFColorindex = rng.Parent.Evaluate(sF1)
End If
If CFColorindex Then
If Not IsNull(oFC.Interior.ColorIndex) Then
CFColorindex = oFC.Interior.ColorIndex
Exit Function
End If
End If
Next oFC
End If 'rng.FormatConditions.Count 0
End Function

My conditional formating is this:

=IF(AND($D5<$G5;$D5<0);"TRUE") make cell d5 BLUE

=IF(AND($D5$G5;$D5<0);"TRUE") make cell d5 GREEN

=$D5=0 make cell d5 BLACK

My formula is this: =CFColorindex(D5)

but it returns #VALUE!.

What am I doing wrong ?

Thank you in advance.