How to format/shade alternating rows in Excel 2

[jw45]

I have an Excel sheet with data like:

253549 200529 C-8661-A 265
KB18392 200529 C-8571-A 100
36080 KB18129 200529 C-8656-A 300
36198 262551 200529 C-8149-A 172
36198 262552 200529 C-8571-A 74
36301 258235 200529 C-7651-B 400
200529 Total 1311
36301 259342 200530 C-8661-A 400
36316 261079 200530 T-6339-A 35
36316 254336 200530 C-8656-A 300
36353 257984 200530 40SBB64JO1970 100
200530 Total 835


I want to shade / not shade the entire row each time the value in column 'A' changes. If 'A' is blank then I want to use column 'B' to determine shading. Also, if a number in 'A' crosses a totals line then I want to toggle the shading.

Results:
[highlight] 253549 200529 C-8661-A 265[/highlight]
KB18392 200529 C-8571-A 100
[highlight]36080 KB18129 200529 C-8656-A 300[/highlight]
36198 262551 200529 C-8149-A 172
36198 262552 200529 C-8571-A 74
[highlight]36301 258235 200529 C-7651-B 400[/highlight]
200529 Total 1311
36301 259342 200530 C-8661-A 400
[highlight]36316 261079 200530 T-6339-A 35
36316 254336 200530 C-8656-A 300[/highlight]
36353 257984 200530 40SBB64JO1970 100
200530 Total 835


Does anybody know a quick way of doing this?

Thanks in advance,
JW

Business conventions are important because they demonstrate how many people a company can operate without.

 

[macropod]

Hi JW,

You can easily do with with conditional formatting. For example, the conditional formatting formula:
=MOD(ROW(),2)=0
can be used to control the colour of the underlying cell. Simply set up the conditional format colouring you want, then copy the cell format to all cells that you want to control.

Cheers

 

[macropod]

Oh, I missed the second part of the problem. To control that, you can extend the conditional format test to check for whether a particular cell contains the word 'Total'. For example:
=AND(MOD(ROW(),2)=0,$C1<>"Total")
in row 1 and copied down as far as needed would test for the word 'Total' on the same row in column C.

Cheers

 

[jw45]

Thanks macropod.

That worked great for changing the format on every other line.

However, I only want to change the format when the value in the first column changes. Note in the example above, I want 36198 and 36316 format the same.

This may be asking for too much but if the value in the first column 'A' is empty (except totals row) then change formatting based in the value in column 'B'.

Thanks again,
JW

 

[macropod]

Hi JW,

Sorry, back to the drawing board ...

Cheers

 

[DaOtH]

Hi JW,

I won't interrupt Macropod finding the anwser for you,
however, are you familiar with ASAP Utilities

This does a lot of the stuff and more you might be looking at, but i can imagine you want to have it in your own workbook.

&quot;In three words I can sum up everything I've learned about life: it goes on.&quot;
- Robert Frost 1874-1963

 

[SkipVought]

Hi,

write a formula that incriments each time the values change, with the exception of the TOTAL rows...
[tt]
F2: =IF(C2="Total",F1,IF(ISBLANK(A2),IF(B2<>B1,F1+1,F1),IF(A2<>A1,F1+1,F1)))
[/tt]
then in the CondFormat, assuming that Total is in column C...
[tt]
Cond1 Formual is: =$C2="Total" /No Shading
Cond2 Formual is: =MOD($F2,2)=1 /Shade1
Cond3 Formual is: =MOD($F2,2)=0 /Shade2
[/tt]


Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[AnotherHiggins]

Hi jw45,

This was just recently covered in the Microsoft: Office Forum in thread68-1072013. This solution will shade alternate records, not just the first of each new record, but it might work for you.

Credit for the following goes to TonyJollans:

If you want to use CF, try this - set it as a formula on the whole sheet if you like:
[COLOR=blue white]=MOD(SUMPRODUCT(($A$2:$A2<>$A$1:$A1)*1)+1,2)=0[/color]

Hope that helps.

[tt]_____
[blue]-John[/blue]
[/tt][red]"I'm against picketing, but I don't know how to show it."[/red]

-Mitch Hedberg

Help us help you. Please read FAQ181-2886 before posting.

 

[jw45]

Fisrt, let me say thanks to everyone for helping.

Almost there but still having problems. I get the exact same results below on both Skip's and higgin's(tony) solution. What could I be doing wrong?

[highlight]
36149 258181
36149 258222[/highlight]
36149 258223 <--This line should be yellow
36200 257796
[highlight]36200 258184[/highlight] <--This line should not
36201 258162
[highlight]36159 258186
36168 258190[/highlight]
36168 260612
[highlight]36175 259997
36206 258207
36206 258212
36206 258213[/highlight]
36206 261267 <--This line should be yellow
36207 253542
36207 255648
[highlight]36207 255649
36243 258191[/highlight]

 

[jw45]

Figured out Skip's solution. I was looking at the wrong cell reference in my conditional formatting. Duh!

Still have not figured out why I can't Tony's solution to work.

JW

The original point and click interface was a Smith and Wesson.

 

[TonyJollans]

Hi JW,

I think my post in the other thread wasn't quite correct. The formula counts the number of changes in column A up to the row being checked at the time but, as posted, is for row 2.

So, to enter it as is you can either ..
(a) apply it to the whole sheet but make sure the active cell is in row 2 when you do it, or ..
(b) just apply it from row 2 downwards (still making sure the active cell is in row 2 but it's more likely by default this way), or ..
(c) apply it to the whole sheet but adjust what you enter according to the row of the active cell - for row 1 you will need to use:
[blue][tt] =MOD(SUMPRODUCT(($A$2:$A1<>$A$1:$A65536)*1)+1,2)=0[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[jw45]

Thanks to all of you!!