Format table rows to change colors when first column data change

[Sako]

Hi everyone,

is there a way to change the color of the rows whenever the 1st column value changes?

like:

Apple
Apple
Apple
Orange
Grape
Grape
Kiwi
Kiwi
Kiwi

I would like for instance Apple to be dark blue, orange -> light blue, Grape dark blue again, Kiwi light blue and so goes on

The Excel default would be to switch one line after the other, but I want to customize it to switch to one set of values to another

 

[sheetspread]

http://www.mrexcel.com/forum/excel-...al-basic-applications-change-group-color.html

 

[Andrew Poulsom]

Try these conditional formatting formulas:

=MOD(SUM(1/COUNTIF($A$1:$A1,$A$1:$A1)),2)=1
=MOD(SUM(1/COUNTIF($A$1:$A1,$A$1:$A1)),2)=0

 

[Sako]

Hello Andrew,

where would I put these 2 formulas? Directly in the first column?

Try these conditional formatting formulas:

=MOD(SUM(1/COUNTIF($A$1:$A1,$A$1:$A1)),2)=1
=MOD(SUM(1/COUNTIF($A$1:$A1,$A$1:$A1)),2)=0

 

[Andrew Poulsom]

Assuming your data starts in A1, select it all and apply conditional formatting using those two formulas, with format dark blue for the first and light blue for the second.

 

[Sako]

Hi Andrew,

Do you know other ways to do that?

I applied those two rules but it doesn't seem to highlight correctly.

To give you a bit more details,

The table I have is linked to an MS Access query (not sure if this affects your formula or not).

I selected all the data from A1 and created the 2 formatting rules.

However, your code seems to make sense...

 

[Andrew Poulsom]

The formulas work for me with your sample data. In what way didn't they work for you?

 

[Sako]

Not sure how I can explain this... but i'll try

sometimes, for the same group in column A, as in Kiwi, I could have it highlighted in 2 colors where as it should be only 1 per category.

Also, I tried putting your formula in an excel cell and drag it down to troubleshoot the issue,

=MOD(SUM(1/COUNTIF($A$2:$A2,$A$2:$A2)),2)=1

The first value of a category always returns TRUE and the rest of values below it returns False
(I changed A1 by A2 because my first row is a header.

Thanks!

=MOD(SUM(1/COUNTIF($A$2:$A2,$A$2:$A2)),2) returns 1 for the first value,
=MOD(SUM(1/COUNTIF($A$2:$A2,$A$2:$A2)),2) returns 0.5 for the second value
=MOD(SUM(1/COUNTIF($A$2:$A2,$A$2:$A2)),2) returns 3 for the third value

Isnt the MOD function supposed to always return a whole number? im a bit confused

 

[Sako]

I am not sure if this can help,

but if I use this formula in a column ZZ,

=1 - MOD((A2=A1) +B1, 2)

it returns me 1 and 0 for each category successfully and has no errors.

But if I apply this same rule in the conditional formatting, the highlight is good for all the values except for the first LAST value per category. Do you know how I can fix the last one?

*************************
please ignore this post, it forces me to have a dummy column B just to stores 0s and 1s...

I'll keep trying

 

[Sako]

FOUND THE SOLUTION!!

=ISODD(SUM(1/COUNTIF($A$1:$A1,$A$1:$A1)))

after doing some search i found this article too:

Excel 2007 : Highlight every other group of rows

thanks Andrew for leading me in the right direction!!