Apply gradient color to a whole row

New Contributor



I know I can use a gradient for a column but I'd like that each row is colored based on the color of the second row

2 Replies


Conditional formatting with color scales, icon sets or data bars only works on the cells with the values; there is no way to extend it to other cells.

You'd have to create multiple rules of type 'Use a formula to determine which cells to format', one for each color.

For example:

  • Select the rows you want to format.
  • As an example, I will assume that you selected rows 2 and down, and that you want to color them based on the value of column B.
  • The active cell in the selection should be in row 2 (the top row of the selection.
  • Select Conditional Formatting > New Rule...
  • Select 'Use a formula to determine which cells to format'.
  • Enter the formula =$B2=10
  • B is the column you want to look at, and 2 is the row of the active cell; 10 is the value to check.
  • Click Format...
  • Activate the Fill tab.
  • Select red.
  • Click OK, then click OK again.
  • Repeat, but with formula =$B$2=20 and a lighter color red, etc.

@Hans Vogelaar 

That's too bad, in my case this manual set won't be suitable. Anyway, thanks for the reply