Conditional formatting (or VBA) to add same background colour as condition formatting in another cell

[tonywatsonhelp]

Hi Everyone,
I have conditional formatting in Column J (range J30:J130) using the Colour Series formatting giving me a colour heat map red to green
the annoying part is i cant get it to colour the rest of the lines (range H30:I130)

so what i need is a way for vba or more conditional formatting to colour the rows in (range H30:I130) the same as is next to them in J

Anyone know if this is possible and how to do it? (happy to change what i'm currently doing if need to)


Thanks

Tony

 

[Fluff]

How about

Sub VBColourScales()
   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
   
   'green (low) to red (high) colour scale
   LowClr = Array(99, 190, 123)     'Green
   MidClr = Array(255, 235, 132)    'Yellow
   HighClr = Array(248, 107, 107)   'Red
   
   Set Rng = Range("J30:J130")
   
   Mn = Application.Min(Rng)
   Mx = Application.Max(Rng)
   Av = Application.Percentile(Rng, 0.5)
   
   For Each Cl In Rng
      If Cl <= Av Then
         Cl.Offset(, -2).Resize(, 3).Interior.Color = GetColour(LowClr, MidClr, (Cl - Mn), (Av - Mn))
      Else
         Cl.Offset(, -2).Resize(, 3).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

 

[tonywatsonhelp]

Excellent,
Thank you fluff,
This will do nicely
Tony

 

[Fluff]

You're welcome & thanks for the feedback