Color code every other group of cells

[toddlerzz]

I need to color code a group of cells every time a value changes.

For example, in column A, I have the word Truck in the first 5 cells, then the word Horse in the next 5 cells, then the word Plane in the next 8 cells, etc.

I need a formula to color cod each group of cells whenever the value changes, it can be just two colors, Truck could be blue, then Horse could be no color, Plane would be blue, next word would be no color, I just need something to differentiate each block when the text changes.

Thanks,

 

[Yongle]

NOTE
- method below assumes that each "group" of words is unique (ie there is only one group of dogs, one fleet of planes etc!)


Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][th]

J​

[/th][th]

K​

[/th][th]

L​

[/th][th]

M​

[/th][th]

N​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]cat[/td][td]cat[/td][td]dog[/td][td]dog[/td][td]dog[/td][td]plane[/td][td]plane[/td][td]plane[/td][td]plane[/td][td]plane[/td][td]pear[/td][td]pear[/td][td]pear[/td][td]pear[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]

1​

[/td][td]

1​

[/td][td]

2​

[/td][td]

2​

[/td][td]

2​

[/td][td]

3​

[/td][td]

3​

[/td][td]

3​

[/td][td]

3​

[/td][td]

3​

[/td][td]

4​

[/td][td]

4​

[/td][td]

4​

[/td][td]

4​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]

TRUE​

[/td][td]

TRUE​

[/td][td]

FALSE​

[/td][td]

FALSE​

[/td][td]

FALSE​

[/td][td]

TRUE​

[/td][td]

TRUE​

[/td][td]

TRUE​

[/td][td]

TRUE​

[/td][td]

TRUE​

[/td][td]

FALSE​

[/td][td]

FALSE​

[/td][td]

FALSE​

[/td][td]

FALSE​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]


1. Explanation
The formula in rows 3 & 4 are there so that you can see how this works

To get cumulative count of unique values starting at A1
- in A3 copied across
=SUMPRODUCT(1/COUNTIF($A$1:A$1,$A$1:A$1&""))

Convert those value to TRUE\False by wrapping above formula in ISODD
- in A4 copied across
=ISODD(SUMPRODUCT(1/COUNTIF($A$1:A$1,$A$1:A$1&"")))

2. Apply conditional formatting using formula in A4

Select row 1
click on Home tab
click on Conditional Formatting
click on New Rule
click on Use a formula to determine which cells to format
paste formula =ISODD(SUMPRODUCT(1/COUNTIF($A$1:A$1,$A$1:A$1&""))) into box under Format values where this formula is true
click on Format
above example was formatted by clicking on Font and changing Color to red

Conditional Formatting can be tricky
- the formula must be correct for the leftmost cell in the range you are formatting
- I suggest you copy what I did EXACTLY (including putting the values in row 1)
- you can then insert rows above or columns to left of data and Excel auto-adjusts
- otherwise you need to amend the formula before trying to use it in conditional formatting

 

[Yongle]

Oops
- I applied to row1 instead of column A
- will update thread shortly

 

[Yongle]

1. Same method as above except the formula is now
=ISODD(SUMPRODUCT(1/COUNTIF($A$1:$A1,$A$1:$A1&"")))

2. Apply conditional formatting using formula above
Select Column A
click on Home tab
click on Conditional Formatting
click on New Rule
click on Use a formula to determine which cells to format
paste formula =ISODD(SUMPRODUCT(1/COUNTIF($A$1:$A1,$A$1:$A1&""))) into box under Format values where this formula is true
click on Format
above example was formatted by clicking on Font and changing Color to red

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]cat[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]cat[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]dog[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]dog[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]dog[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]plane[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]plane[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]plane[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]plane[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td]plane[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td]pear[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td]pear[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13​

[/td][td]pear[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14​

[/td][td]pear[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

15​

[/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

 

[Gerald Higgins]

Another way, perhaps a little simpler, also uses a helper row, and again assumes your data is pre-sorted, with no "split" groups

In A3, enter the single value
1

In B3
=if(B1=A1,A3,-A3)

And copy across.

This will give you a sequence of 1 and -1, which flips from 1 to -1 each time the data in A1 changes.
Then apply Conditional Formating, based on whether the value in row 3 = 1 or -1.

Edit to add - this assumes your data is laid out as in post #2 , in a ROW.
Just noticed, the OP said you had data in a column.
This (and Yongle's) solution can easily be adapted to a column - post back if you need help with that.