Conditional Format gradient low number to high number

[Peter Davison]

I have a range of rows/columns starting at I10 to AA10 and the number of rows can be different.
I have been looking for some code that can add this gradient conditional format but only if Cell D7 = "Y"

Each row needs its own gradient rather than all rows in the same gradient.

I found the code below from Fluff but I cant work out how to make it work for what I need?

If you can help it would be appreciated.

This is the code I found and have just changed the sheet name/cells

Sub GradientColour()
Dim Cl As Range, Rng As Range
Dim Mn As Long, Mx As Long, Av As Long
Dim LowClr As Variant, MidClr As Variant, HighClr As Variant

LowClr = Array(99, 190, 123)
MidClr = Array(255, 235, 132)
HighClr = Array(248, 107, 107)

Set Rng = Range("I10:AA10", Range("I" & Rows.Count).End(xlUp))

Mn = Application.Min(Rng)
Mx = Application.Max(Rng)
Av = Application.Percentile(Rng, 0.5)

Sheets("Report Writer").Select
Range("I10").Select

For Each Cl In Rng
If Cl <= Av Then
Cl.Offset(, 1).Interior.Color = GetColour(LowClr, MidClr, (Cl - Mn), (Av - Mn))
Else
Cl.Offset(, 1).Interior.Color = GetColour(HighClr, MidClr, (Cl - Mx), (Av - Mx))
End If
Next Cl
End Sub
Function GetColour(BaseClr As Variant, MidClr As Variant, Dif As Long, Avg As Long) As Long
Dim i As Long
Dim Clr As Double

For i = 0 To 2
Clr = (MidClr(i) - BaseClr(i)) / Avg
GetColour = GetColour + Int((Dif * Clr + BaseClr(i))) * 256 ^ i
Next i
End Function

 

[Fluff]

Duplicate to: Gradient Colour scale for a group of cells based on another cell showing "Y"

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.