Public Macro Help (CFColorindex)

[carl]

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.

 

[Bob Phillips]

Works fine for me fine.

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[carl]

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:

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]

Carl,

Whilst the IF...TRUE is not necessary, that is not the problem. I have it
in my test and it works fine. The BLUE v BLACK issue might be because it
hits that condition first, they are not unique enough. Try


=AND($D5<>0,$D5<$G5) make cell d5 BLUE

=AND($D5<>0,$D5>$G5) make cell d5 GREEN

=$D5=0 make cell d5 BLACK


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)