Using A Public Function /
 

Thanks Bob Phillips for pointing me to this very interesting
site...http://www.xldynamic.com/source/xld.CFConditions.html

I am trying to use one the Public Functions listed there buat am having
trouble. This function is (I think) used to determine the color index number
of a given cell that has been conditionally filtered.

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



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 filtering formulae a

=$E5$H5 / Blue

=$E5<$H5 / Green

How do I use the Public Function ?

Thank you in advance.

Using A Public Function /
 

What problem are you experiencing?

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"carl" wrote in message
...


Thanks Bob Phillips for pointing me to this very interesting
site...http://www.xldynamic.com/source/xld.CFConditions.html

I am trying to use one the Public Functions listed there buat am having
trouble. This function is (I think) used to determine the color index
number
of a given cell that has been conditionally filtered.

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



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 filtering formulae a

=$E5$H5 / Blue

=$E5<$H5 / Green

How do I use the Public Function ?

Thank you in advance.