In Excel increment a gradient colour based on cell value

Question

How can I increment the gradient colour to match a value given in a cell.

This is a mock up using cells to show the output I want.

What happens when using gradients that the length of the line shrinks but all the gradient colour stops remain. How can I stop the gradients at the colours based on value.

This is a bar chart with gradients as you can see the line length changes but all colour stops still show.

Answer 1

Similar one to Imran's solution but with following changes,

1. A different formula for validation
2. Use of Excel Tables so that you can add / remove rows and keep the conditional formatting range dynamic

Once you have the initial data set ready please convert the data range including the columns for color fill to an Excel Table (Select the entire range including headers -> press Ctrl + T (OR) Click on 'Insert' Tab in Ribbon -> Under 'Tables' section click on 'Table')

Once you get your data set converted to an excel Table select the column range (here B2: B4) within the table for applying 'RED' fill Shortcut: Select any cell in the column B within the table and press Ctrl + <Space Key>)

Goto Conditional Formatting -> New Rule -> Use a formula to determine which cells to format

RED =COLUMN(B$2)-COLUMN($B2)+1<=$A2

Click 'OK' and hit 'Apply'

Repeat the same for remaining 4 columns with the below formulas,

ORANGE =COLUMN(C$2)-COLUMN($B2)+1<=$A2

AMBER =COLUMN(D$2)-COLUMN($B2)+1<=$A2

L GREEN =COLUMN(E$2)-COLUMN($B2)+1<=$A2

D GREEN =COLUMN(F$2)-COLUMN($B2)+1<=$A2

Here is the screenshot showing the formula used,

The conditional formatting range will automatically expand / contract when rows are added / deleted.

Cheers !

Answer 2

Apply formulas right next to the question rating and then do conditional formatting as per my screenshot.

The formula is to fill the cells with value 1 or 0 , so if you have 1 in the cell it should be colored otherwise no fill.