Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

EXCEL: Highlighting Alternating Group of Rows 1

Status
Not open for further replies.

tjcusick

Programmer
Dec 26, 2006
134
US
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
 



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][glasses]Don't let the Diatribe...
talk you to death![tongue][/sup][sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
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.
 



oops.

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

[/tt]

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



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

Skip,
[sup][glasses]Don't let the Diatribe...
talk you to death![tongue][/sup][sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
tjcusick said:
Ah sorry I am using Excel 2007
Me too. I feel your pain. [wink]

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.
 



I try avoidance as much as possible.

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

Part and Inventory Search

Sponsor

Back
Top