ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Public Function - Color Index (https://www.excelbanter.com/excel-worksheet-functions/82176-public-function-color-index.html)

carl
Public Function - Color Index
 
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(ad;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
Public Function - Color Index
 
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" wrote in message
...
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(ad;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

carl
Public Function - Color Index
 
Thnks for checking. It does not work for me. Could there be a problem because
I am using 3 conditions ?

"Bob Phillips" wrote:

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" wrote in message
...
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(ad;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

carl
Public Function - Color Index
 
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!.

"Bob Phillips" wrote:

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" wrote in message
...
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(ad;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


All times are GMT +1. The time now is 01:51 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com