Conditional formatting entire row with graded color scale

**OU405** · 04-23-2010, 03:18 PM

Hello all,

Attached is a spreadsheet with a graded color scale applied to the values in column D that have a value greater than or equal to 40. I have also filled the rows when the value in column D is greater than or equal to 40 with various colors depending on the value. How would I go about applying the graded color scale to the entire row instead of using several rules?

Thanks

**6StringJazzer** · 04-23-2010, 03:59 PM

Open Conditional Formatting, Manage Rules, and you can update the rules for graded scale that refer to

=$D$2:$D$21

to be

=$A$2:$D$21

and remove the other rules.

**OU405** · 04-23-2010, 04:18 PM

Thanks for the reply.

When I do that, the values in each cell are conditionally formatted based on the criteria provided. This is not what I want to accomplish. I want the colors in columns A-C to match the color in D for each row. I added the other rules to kind of show what I want to happen with each row. If there is no way to apply the color scale to rows I can get by with making up however many rules I need to.

**6StringJazzer** · 04-23-2010, 07:50 PM

I'm sorry, my bad. I haven't done much with the gradient formatting introduced in Excel 2007 and misunderstood what was going on.

I could not find a way to do this, because gradient formatting seems restricted to the values in the formatted cell, and cannot be done with formulas as can be done with plain vanilla formatting. As far as I can tell.

I tried to develop a VBA solution but have found that Interior.Color is the base color of the cell and does not change when gradient conditional formatting changes the fill color.

So...I'm sorry but I ran out of ideas.

**OU405** · 04-24-2010, 12:19 AM

Thanks for trying. I messed with it for so long today, but could not come up with anything either.

**Inasmuch** · 10-18-2016, 11:50 AM

Hi,

This thread is super old but I've been trying to accomplish that very thing all day today and now that I have a solution I'm just too excited not to share it. It's 7 lines of VBA code and the elegance makes me weep.

Disclaimer: This works for Excel 2010, so no guarantees for other versions.

Please Login or Register  to view this content.

This code looks at the color actually displayed in each conditionally formatted cell and applies that to every cell to its left. In this case, the gradient was applied to column 6 and each column has headers (hence row starts at 2).
Make sure to adapt the numbers to your spreadsheet and you should be good to go.

Let us rejoice! Hurrah! Hurrah!

**6StringJazzer** · 10-18-2016, 12:35 PM

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #3 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer