conditional formatting

[handlewithcare535]

I'm trying to make a spreadsheet so that everytime I add a new row, the background is a different colour. I want two colours that alternate as you go down the rows so that it is easier to read. Is there a way to do this automatically when I type in text? I have the following code, which works, but there's going to be a lot of 'if' statements if I try to continue this for my whole sheet!!

If Range("K3") = "odd" Then
  Range("a3:e3").Select
With Selection.Interior
         .ColorIndex = 35
         .Pattern = x1Solid
         .PatternColorIndex = x1Automatic
End With

ElseIf Range("K3") = "even" Then
  Range("a3:e3").Select
With Selection.Interior
         .ColorIndex = 36
         .Pattern = x1Solid
         .PatternColorIndex = x1Automatic
End With

End If

Obviously the "k3 = odd" part refers to a cell that states whether the row number is odd or even.

Is there any clever way of doing this?

Thanks

hwc535

 

[Rofeu]

Hi,

as you state in your title. Use the Conditional Formatting (under Format) that Excel provides. Change 'When Cell Value is' to 'When Formula is' and use the MOD function in combination with ROW to test for Odd or Even.

Cheers,

Roel

 

[GlennUK]

As Rofeu says, use Conditional Formatting with a formula using ROW function, like one of these maybe:

=MOD(ROW(),2)
=INT(ROW()/2)*2-ROW()
=(EVEN(ROW())-ROW())

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[handlewithcare535]

Many thanks guys, I'll try this now. I've also just been asked if it's possible to turn the row red if the task is overdue. This is simple for the 'date due' cell, but can I do it for the whole row?

Thanks

hwc535

 

[Rofeu]

Much the same thing. Use the conditional formatting with the formula to test that cell for the row. (=$A1>[due date] , assuming select all cells selected and Col A contains 'Date due')

Cheers,

Roel