| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Public Function Problems | Excel Discussion (Misc queries) | |||
| UDF help please | Excel Discussion (Misc queries) | |||
| Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
| Conversion | Excel Worksheet Functions | |||
| HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions | |||
Linear Mode
