| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Public Macro Help (CFColorindex)
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Public Macro Help (CFColorindex)
Works fine for me fine.
Did you put CFColorindex in a standard code module (InsertModule), not a sheet module, and not ThisWorkbook? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "carl" wrote in message ... 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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Public Macro Help (CFColorindex)
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: =$D5<$G5 make cell d5 BLUE =$D5$G5 make cell d5 GREEN =$D5=0 make cell d5 BLACK 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" wrote: Works fine for me fine. Did you put CFColorindex in a standard code module (InsertModule), not a sheet module, and not ThisWorkbook? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "carl" wrote in message ... 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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Public Macro Help (CFColorindex)
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) "carl" wrote in message ... 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: =$D5<$G5 make cell d5 BLUE =$D5$G5 make cell d5 GREEN =$D5=0 make cell d5 BLACK 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" wrote: Works fine for me fine. Did you put CFColorindex in a standard code module (InsertModule), not a sheet module, and not ThisWorkbook? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "carl" wrote in message ... 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Search, Copy, Paste Macro in Excel | Excel Worksheet Functions | |||
| Editing a simple macro | Excel Worksheet Functions | |||
| macro with F9 | Excel Discussion (Misc queries) | |||
| Make Alignment options under format cells available as shortcut | Excel Discussion (Misc queries) | |||
| Playing a macro from another workbook | Excel Discussion (Misc queries) | |||
Linear Mode
