FormatConditions for ColorIndexes in Excel

[mac7attack]

Hi,

I have the following code to chagne the the colors of alternating rows. Even rows are colored Green(Index of 4) and odd rows are colored yellow (index of 6). Is there any way to modify these conditions to color Row 1 a different color, say blue (index of 33)?

Worksheets("Sheet1").Cells.Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=MOD(ROW(),2)=0"
Selection.FormatConditions(1).Interior.ColorIndex = 4
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=MOD(ROW(),2)=1"
Selection.FormatConditions(2).Interior.ColorIndex = 6

 

[Zathras]

This is not tested, but based on your code, since you can have up to three conditions, you should be able to modify it like this:

Worksheets("Sheet1").Cells.Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=ROW()=1"
Selection.FormatConditions(1).Interior.ColorIndex = 33
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=MOD(ROW(),2)=0"
Selection.FormatConditions(1).Interior.ColorIndex = 4
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=MOD(ROW(),2)=1"
Selection.FormatConditions(2).Interior.ColorIndex = 6

 

[mac7attack]

I tried adding a third condition and teh thrid condition overruled the first. Since row 1 is an odd row, then the last condition take precedent.
But i found a better way of doing it.
With later defining the color for Row 1 when i define the content of the row.
Thanks

If x Mod 2 = 0 Then
       Worksheets("Sheet1").Cells(x,1).EntireRow.Interior.ColorIndex = 4
    ElseIf x Mod 2 = 1 Then
        Worksheets("Sheet1").Cells(x,1).EntireRow.Interior.ColorIndex = 6
    End If

 

[Zathras]

Sorry you couldn't get it to work. As I said it was untested. All that was needed to fix it was to update the index numbers like this (slightly rearranged for clarity):

  With Worksheets("Sheet1").Cells
    With .FormatConditions
      .Delete
      .Add Type:=xlExpression, Formula1:="=ROW()=1"
      .Add Type:=xlExpression, Formula1:="=MOD(ROW(),2)=0"
      .Add Type:=xlExpression, Formula1:="=MOD(ROW(),2)=1"
    End With
    .FormatConditions(1).Interior.ColorIndex = 33
    .FormatConditions(2).Interior.ColorIndex = 4
    .FormatConditions(3).Interior.ColorIndex = 6
  End With