Solved

Alternate row color based on column value

Posted on 2014-03-17
11
1,964 Views
Last Modified: 2014-03-31
Screen shot
Excel 2010

I am trying to highlight the alternate row but if column D is the same it should have the same color.  Please review the embeded image
0
Comment
Question by:itsmehar
11 Comments
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 39934843
0
 
LVL 49

Accepted Solution

by:
Rgonzo1971 earned 125 total points
ID: 39935073
Hi,

You could use a helper's column with this formula

=IF(D2<>D1,NOT(E1),E1)

Open in new window

in the conditional formatting use this formula
=E2

Open in new window


See example
EE20140317.xlsx
0
 

Author Comment

by:itsmehar
ID: 39935420
Rgonzo1971,
Your suggestion works, but it only highlights the first column. How do i apply it ot the entire row.
0
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 39935714
If you apply two conditional formats as in the article I attached, then use paintbrush to apply this formatting to the whole sheet, or wherever you want.
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 39935717
Not for points

To make Rgonzo's suggestion work for the whole row set the "applies to" range to encompass all columns and then change formula to

=$E2

regards, barry
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 7

Expert Comment

by:COACHMAN99
ID: 39935725
Technique 1: Apply shading to alternate rows by using conditional formatting

One way to apply shading to alternate rows in your worksheet is by creating a conditional formatting rule. This rule uses a formula to determine whether a row is even or odd numbered, and then applies the shading accordingly. The formula is shown here:

=MOD(ROW(),2)=0
1.On the worksheet, do one of the following:
To apply the shading to a specific range of cells, select the cells you want to format.
To apply the shading to the entire worksheet, click the Select All button.
0
 
LVL 2

Expert Comment

by:bearblack
ID: 39936994
To expand:

You will need two conditional formatting rules after selecting the entire table

The first will require the addition of a column with the formula:
=IF(OR(D2=D3,D2=D1),1,0) (see Attached file)
then create a conditional formatting rule = $F2=1 (Make sure this is the first rule in the rules manager)

The Second as stated above by coachman99 to highlight the alternate rows
Alternate-Row-Formating.xlsx
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 39937880
Hello COACHMAN99,

itsmehar doesn't want to apply conditional formatting to alternate rows, he wants the formatting to be applied to alternate groups of rows based on the entry in column D.

bearblack,

Your suggestion doesn't fulfil the requirement either.

As the asker says, Rgonzo's solution works, he just needed it applied to the whole row, which you can do with my suggested adjustment

regards, barry
0
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 39938384
the spec says
'highlight the alternate row but if column D is the same it should have the same color'

I assumed this means
1. add alternate shading
2. add additional conditional formatting if column D is same as row above
0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 125 total points
ID: 39938513
OK, fair point COACHMAN, I can see why you might think that but I think the screenshot makes it clear that alternate groups of data need to be highlighted, each group of same numbered data in column D.

That's what Rgonzo suggested and istmehar says it works so I guess that's the requirement, just needed to be expanded to format all columns.

If you want you can do that without a helper column

I selected the range A2:D22 and applied this formula in conditional formatting which will work with ranges up to 1000 rows

=AND($D2<>"",MOD(SUM(($D2:$D$1001<>$D1:$D$1000)+0),2))

see attached

regards, barry
cf-barry.xlsx
0
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 39938516
I see - thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Insert Column Macro 3 38
Hiding column macro 10 28
Excel Formula 4 28
Excel - find text within text? 1 23
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now