Conditional formating of several non-squencial cells

[timely]

I am trying to write code that will check only certain cell dependent on row. In this case certain cells that are below a given number are red and rest are green. How ever I am getting an error:
Run-time error '1004': Unable to set the ColorIndex property of the Interior class

Here is the code:
Dim sCell As String
Dim cCell As Integer

sCell = "D"
For cCell = 10 To 47
If cCell = 18 Or cCell = 27 Or cCell = 28 Or cCell = 32 Or cCell = 41 Or cCell = 42 Or cCell = 46 Or cCell = 47 Then
Range(sCell & cCell).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=$I" & cCell
Selection.FormatConditions(3).Interior.ColorIndex = 3
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual _
, Formula1:="=$I" & cCell
Selection.FormatConditions(3).Interior.ColorIndex = 10
'ElseIf cCell = 17 Or cCell = 30 Or cCell = 31 Or cCell = 34 Or cCell = 35 Or cCell = 40 Or cCell = 43 Or cCell = 45 Then
' Range(sCell & 18).Select
' Selection.FormatConditions.Delete
' Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
' Formula1:="=$I$cCell"
' Selection.FormatConditions(2).Interior.ColorIndex = 3
' Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual _
' , Formula1:="=$I$cCell"
' Selection.FormatConditions(3).Interior.ColorIndex = 10
End If
Next
End Sub


Tim

 

[SkipVought]

Hi,

Without the code, can you define any one cell in column D using Format/Conditional Formatting?

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[PHV]

Perhaps this ?
...
Selection.FormatConditions([highlight]1[/highlight]).Interior.ColorIndex = 3
...
Selection.FormatConditions([highlight]2[/highlight]).Interior.ColorIndex = 10
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[timely]

Thanks PH

that was a simple miss on my part.

Tim

 

[Zathras]

Allow me to suggest that as a programmer, you might want to set it up this way, separating the detail work of establishing the conditional formatting from the higher-level logic of which rows you need to be affected:

Option Explicit
Sub test()
    SetConditionalFormat 18, 27, 28, 32, 41, 42, 46, 47
End Sub

Sub SetConditionalFormat(ParamArray Rows() As Variant)
[COLOR=green]' Sets conditional formatting in column "D" for specified rows.
' Condition is based on value in column "I" of each corresponding row.
' Where the value at Dnnn is less than the value at Innn,
'   the background color index is set to 3 (Red)
' Otherwise the background color index is set to 10 (Green)[/color]
Dim i As Integer
  For i = LBound(Rows) To UBound(Rows)
    With Range("D" & Rows(i))
      With .FormatConditions
        .Delete
        .Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=RC[5]"
        .Add Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:="=RC[5]"
      End With
      .FormatConditions(1).Interior.ColorIndex = 3
      .FormatConditions(2).Interior.ColorIndex = 10
    End With
  Next i
End Sub