Alternate row Colour/Border dependant on Cell Value

[deeppurple247]

I was wondering if anyone knows if it's possible to set the background colour for the rows in a list depending on what the cell values are. See bellow...

Z1 A1
Z1 A1
Z1 A1
Z1 A2
Z1 A2
Z1 A2
Z1 A3
Z1 A3
Z1 A3
Z1 A4
Z1 A4
Z1 A4


I have tried to illustrate this as best I can. If the above list represents the first collumn in a spreadsheet the row colours need to alternate as illustrated. There should only be two colours in the spreadsheet. This is just a way to seperate the different entries according to the first row instead of using borders.

If anyone knows how it could be approached that'd be great

Alex

[MSP77079]

Assume that the A values are in column B. Use Format >> Conditional Formatting. When the dialog box comes up, change the default "Cell Value is" to "Formula Is".

Put this formula in for the first condition:

=EVEN(VALUE(RIGHT($B1,LEN($B1)-1)))=VALUE(RIGHT($B1,LEN($B1)-1))

And set the font color to blue.

Add a second condition. Put this formula in for the second condition:

=NOT(EVEN(VALUE(RIGHT($B1,LEN($B1)-1)))=VALUE(RIGHT($B1,LEN($B1)-1)))

And set the font color to red.

If the logic is something other than the last number being even/odd, you would need to work on the logic for that. You need a formula that returns TRUE or FALSE. You can only have up to 3 conditions.