Public Function - Color Index

[carl]

I am trying to use the Public Function below. I get "#value!".

I have three conditional formating conditions:

If(and(a<d;a<>0);"TRUE") color cell blue
If(and(a>d;a<>0);"TRUE") color cell green
a=0 color cell black

Do I need to modify the Public Function ? Thank you in advance.


Public Function CFColorindex(rng As Range)

'---------------------------------------------------------------------

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

 

[Bob Phillips]

assuming that your formulae are really

=IF(AND(A1<D1;A1<>0);"TRUE")

or some other row, and you the semi-colon separator in your Excel and you
used

=CFColorindex(A1)

in the worksheet, it works fine, and returns 5 as it should when the
condition is satisfied, and FALSE when not.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[carl]

Thnks for checking. It does not work for me. Could there be a problem because
I am using 3 conditions ?

 

[carl]

hi again. i can get the function to work if I use a formula like:

A1<D1 color cell green.

When I add the If(and the funtion returns #value!.