EXCEL: Highlighting Alternating Group of Rows 1

[tjcusick]

I would like to highlight Alternating Group of Rows based on one particular column.

The spreadsheet has an EMP_CO column and each EMP_CO number could have multiple rows. This spreadsheet is sorted on the EMP_CO column. What i would like to do is for each alternating group of EMP_CO numbers i would like to highlight that particular group of rows.

Anyone know if this is doable?

Thanks in advance..

Tom Cusick

 

[SkipVought]

Hi Tom,

You can use the Format > Conditional Foramtting... feature, but first, your groupings must be numbered, using a formula in an adgacent column.

Assuming that your headings are in row 1 and your first row of data is in row 2 and assuming that EMP_CO is in columns A and assuming that the adjacent column is Z...
[tt]
=if(A2=A1,Z1,Z1+1)
[/tt]
This will number your groupings.

Then select all the cells you want to shade and set the color to ONE of the shades.

Next Format > Conditional Formatting..., select the Formula is dropdown and then enter...
[tt]
=MOD($A2,2)
[/tt]
then select the formatting PATTERN you want for the opposite shade.

VOLA y'all!

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AnotherHiggins]

I knew I had seen this before in a single Conditional Format. Then I found this thread:
[tab]thread68-1500047
where I posted the solution. I'm not only forgetting stuff, but I'm forgetting that I ever even knew it....

Anyway, when playing with that solution in excel 2007 I found the problem that jrobin must have run into. So I'll amend my previous formula.

Assuming that you're in a pre-2007 version of excel, then:
[ul][li]Select the columns that you want to conditionally format[/li]
[li]Go to Format > Conditional Formatting[/li]
[li]Change the first box to Formula Is[/li]
[li]Copy the following formula and paste into the next box:[/li]
[ul][li][COLOR=blue white]=MOD(INT(SUM(1/COUNTIF($A$1:$A1,$A$1:$A1))),2)=0[/color][/li][/ul]
[li]Select the Format button[/li]
[li]Go to the Patterns tab and select the color you want[/li]
[/ul]

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

oops.

reading John's post, I realize that I forgot...
[tt]
=MOD($A2,2)=1

[/tt]

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[tjcusick]

Ah sorry I am using Excel 2007

 

[SkipVought]

You can still do it in 2007. Just find the menu that contains conditional formatting.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AnotherHiggins]

Ah sorry I am using Excel 2007

Me too. I feel your pain.

Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.

Paste the formula in there and pick whatever formatting you want applied.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

I try avoidance as much as possible.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]