Conditional formatting entire row with graded color scale

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.

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.

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.

Sub Spread_Gradient()

    Dim row, col, last_row As Integer

    last_row = ActiveSheet.UsedRange.Rows.Count

    For row = 2 To last_row

        For col = 1 To 5

            Cells(row, col).Interior.color = Cells(row, 6).DisplayFormat.Interior.color

        Next col

    Next row

End Sub

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!