Excel - Group Conditional formatting 1

[norty303]

I found a solution that will highlight the first row of each group when the value changes but what i'd like to do is be able to highlight each group of rows with alternating colours where the group sizes are dynamic.

e.g. We have groups identified by a unique ID and are typically clustered in 2, 3 or 4 row groups. I'd like to be able to shade each group with alternating colours to make it easier for the end user to pick out each ID group.

Thanks

Adam

 

[GlennUK]

To shade groups 1,3,5 etc use Conditional Formatting with this formula:

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

To shade groups 2,4,6 etc, add an extra format, with a formula the same but ending in "=0" instead of "=1".


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[norty303]

Excuse my ignorance but how do i go about applying that?

Do i highlight all the rows then Format-->Cond format-->Formula is etc?

That just seems to kill it.

BTW, when i refer to groups, these are not Excel groups, the data is just sorted so that the ID's follow consecutively, resulting in all of the ID's the same being together.

The data list is 200+ rows long so do i need to adjust the formula for this?

 

[GlennUK]

I accidentally put a spurious 2 in my formula ( $A12 should be $A1 ).

Highlight the rows for formatting ( the formula I used assumes the first row is row 1 ), do menu command Format/Conditional Formatting/Formula Is

I got the fact that they were consecutive ID's.

Select the entire range and use my ( corrected ) formula and it should all work.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[norty303]

Thanks.

I'm no novice but when it locked up solid i thought i may be missing a trick!!

 

[norty303]

Ok, i used this formula and it highlighted every row.

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

I then added another of

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

and it changed every row to the colour i've set for this.

Neither seemed to be selecting odd or even groups

Cheers BTW ;-)

 

[norty303]

Sorry, my bad, just spotted the unwanted $'s

Thanks

 

[KenWright]

Just an addendum but if you check out the FAQs you will often find the answer to your question without even having to ask:-

How can I shade alternate rows automatically? faq68-4744

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[norty303]

Yes, but the FAQ only explains how to highlight fixed numbers of rows, not ones that change dynamically.

as i said in my first post, i found a solution that was close but not quite.

 

[GlennUK]

Have you got this working yet, now that you have the $'s in the right places?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[norty303]

Yeah, works like a dream thanks!

 

[GlennUK]

Great!!!

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.