Formula to set cell color

[BrianB]

I have a column containing a sorted list of values which includes duplicate
values such as:

Row 1: 1
Row 2: 1
Row 3: 2
Row 4: 3
Row 5: 3
Row 6: 4
Row 7: 4
Row 8: 4
Row 9: 5

Without using VBA or macros (an unfortunately non-negotiable requirement of
the customer) I'm tasked to do the following using conditional formatting:
1. Make a cell that has the same value as the cell immediately above it the
same color as that of the cell immediately above it
2. Make the color of a cell whose value is not the same as the cell
immediately above it a different color than the cell immediately above it
3. Alternating two colors is fine

For my example data, the cells in rows 1 and 2 should be yellow, row 3
should be blue, row 4 and 5 should be yellow, row 6 through 8 should be
blue, and row 9 should be yellow.

Using Excel 2003 and just formulas, I don't think that this is possible but
I'm hoping that someone out there will know better. I don't know if Excel
2007 has added something to make this possible.

Anyone know of a way to do this?

Brian

 

[JLatham]

I'm going to step out on a limb and say I don't think it can be done with
just conditional formatting either. The conditional format doesn't know what
color the previous cells are. I can set up conditional format with formulas
that will change colors of cells with contiguous groups, but not in
alternating fashion. In your series of data, assuming we default shade
things to Yellow and switch to blue when we have a contiguous group, then:

Row 1: 1 - changed to blue
Row 2: 1 - changed to blue
Row 3: 2 - no change, YELLOW
Row 4: 3 - changed to blue
Row 5: 3 - changed to blue
Row 6: 4 - changed to blue
Row 7: 4 - changed to blue
Row 8: 4 - changed to blue
Row 9: 5 - no change, YELLOW

and I fully realize that's not what you want. You can come close by setting
up formulas that will change the colors of all but the 1st of a series.
Choose row 2 to the end of the list, and in Conditional Formatting

Cell Value Is "equal to" A1
and set the alternative format. That gives us:
Row 1: 1 - no change, YELLOW
Row 2: 1 - changed to blue
Row 3: 2 - no change, YELLOW
Row 4: 3 - no change, YELLOW
Row 5: 3 - changed to blue
Row 6: 4 - no change, YELLOW
Row 7: 4 - changed to blue
Row 8: 4 - changed to blue
Row 9: 5 - no change, YELLOW

 

[Cortez]

I'm going to step out on a limb and say I don't think it can be done with
just conditional formatting either.  The conditional format doesn't know what
color the previous cells are.  I can set up conditional format with formulas
that will change colors of cells with contiguous groups, but not in
alternating fashion.  In your series of data, assuming we default shade
things to Yellow and switch to blue when we have a contiguous group, then:

Row 1:  1 - changed to blue
Row 2:  1 - changed to blue
Row 3:  2 - no change, YELLOW
Row 4:  3 - changed to blue
Row 5:  3 - changed to blue
Row 6:  4 - changed to blue
Row 7:  4 - changed to blue
Row 8:  4 - changed to blue
Row 9:  5 - no change, YELLOW

and I fully realize that's not what you want.  You can come close by setting
up formulas that will change the colors of all but the 1st of a series.  
Choose row 2 to the end of the list, and in Conditional Formatting

Cell Value Is     "equal to"    A1
and set the alternative format.  That gives us:
Row 1:  1 - no change, YELLOW
Row 2:  1 - changed to blue
Row 3:  2 - no change, YELLOW
Row 4:  3  - no change, YELLOW
Row 5:  3 - changed to blue
Row 6:  4 - no change, YELLOW
Row 7:  4 - changed to blue
Row 8:  4 - changed to blue
Row 9:  5 - no change, YELLOW

Assuming you are in A1 to A9

Set B1 value to "Yellow", in B2 use :
=IF(A2=A1,IF(B1="Yellow","Yellow","Blue"),IF(B1="Yellow","Blue","Yellow"))
Extend this down to B9.

Conditional format A1:AX with two rules:
1: formula =if(b1="Yellow",1,0) --> format with yellow fill
2: formula =if(b2="Blue",1,0) --> format with blue fill

You would have to hide column B (or any column on the same or another
perhaps hidden page could be used)

Not the most elegant method, but it works.


TK

 

[BrianB]

I'm going to step out on a limb and say I don't think it can be done with
just conditional formatting either. The conditional format doesn't know
what
color the previous cells are. I can set up conditional format with
formulas
that will change colors of cells with contiguous groups, but not in
alternating fashion. In your series of data, assuming we default shade
things to Yellow and switch to blue when we have a contiguous group, then:

Row 1: 1 - changed to blue
Row 2: 1 - changed to blue
Row 3: 2 - no change, YELLOW
Row 4: 3 - changed to blue
Row 5: 3 - changed to blue
Row 6: 4 - changed to blue
Row 7: 4 - changed to blue
Row 8: 4 - changed to blue
Row 9: 5 - no change, YELLOW

and I fully realize that's not what you want. You can come close by
setting
up formulas that will change the colors of all but the 1st of a series.
Choose row 2 to the end of the list, and in Conditional Formatting

Cell Value Is "equal to" A1
and set the alternative format. That gives us:
Row 1: 1 - no change, YELLOW
Row 2: 1 - changed to blue
Row 3: 2 - no change, YELLOW
Row 4: 3 - no change, YELLOW
Row 5: 3 - changed to blue
Row 6: 4 - no change, YELLOW
Row 7: 4 - changed to blue
Row 8: 4 - changed to blue
Row 9: 5 - no change, YELLOW

Assuming you are in A1 to A9

Set B1 value to "Yellow", in B2 use :
=IF(A2=A1,IF(B1="Yellow","Yellow","Blue"),IF(B1="Yellow","Blue","Yellow"))
Extend this down to B9.

Conditional format A1:AX with two rules:
1: formula =if(b1="Yellow",1,0) --> format with yellow fill
2: formula =if(b2="Blue",1,0) --> format with blue fill

You would have to hide column B (or any column on the same or another
perhaps hidden page could be used)

Not the most elegant method, but it works.


TK

Looks good, TK. I'll give it a try.

Brian