• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2395
  • Last Modified:

Alternate row color based on column value

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
2 Solutions

You could use a helper's column with this formula


Open in new window

in the conditional formatting use this formula

Open in new window

See example
itsmeharAuthor Commented:
Your suggestion works, but it only highlights the first column. How do i apply it ot the entire row.
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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.
barry houdiniCommented:
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


regards, barry
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:

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.
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
barry houdiniCommented:

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.


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
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
barry houdiniCommented:
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


see attached

regards, barry
I see - thanks

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now