Toggle cells background color based on value

You can use a helper column (and then hide it if you want to). Lets say your helper column is Q. In Q2 put in the following formula;

=IF(A2=A1,Q1,1-Q1)

Dragging this down column Q should give you a column of 1s and 0s

Then you can use conditional formatting; Highlight Column A (A2 down)

First rule =$Q2=1 then pick your colour
Add =$Q2=0 then pick your other colour.

If you cannot or don't want to use a helper column, then colour all the cells in Column A (from A2 down) in one colour, highlight column A from A2 down and use this conditional format formula

=MOD(SUMPRODUCT(--(($A$1:INDIRECT(ADDRESS(ROW()-1,1,3,1))=$A$2:INDIRECT(ADDRESS(ROW(),1,3,1)))=FALSE)),2)

then choose another format colour

Two CF rules for A2 (one each for green and blue):

=AND(A2<>"",ISEVEN(SUMPRODUCT(1/COUNTIF($A$2:$A2,$A$2:$A2))))

=AND(A2<>"",ISODD(SUMPRODUCT(1/COUNTIF($A$2:$A2,$A$2:$A2))))

Applies to: $A$2:$A$5000 (don't try to apply it to $A:$A as it won't work correctly)

Make the applies to range as long as you will ever need it to be.

You could halve the computational aspect by doing what was suggeseted by your other helper: apply one of the two colours manually and use just one rule for the other.

It may be that a VBA solution would be more efficient in your circumstances, but I wouldn't be able to help with that.

However, we did solve the issue for you as presented in your opening post.

No, he edited his post long before you read it - you can see the edit time bottom left of the post in question (a few minutes after he originally posted it).

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.