Color scale conditional formatting based on another cell's value

Hi,

This code, placed in the worksheet code section of your file (not a module and not the workbook), will do it:

Private Sub Worksheet_Change(ByVal Target As Range)
' Declare variable CellRange as the cells which will activate this sub
Dim CellRange As Range
    Set CellRange = Range("G4:I6")
' Make each of range B4:D6 the colours from G4:I6
    Range("B4").Interior.Color = Range("G4").DisplayFormat.Interior.Color
    Range("C4").Interior.Color = Range("H4").DisplayFormat.Interior.Color
    Range("D4").Interior.Color = Range("I4").DisplayFormat.Interior.Color
    Range("B5").Interior.Color = Range("G5").DisplayFormat.Interior.Color
    Range("C5").Interior.Color = Range("H5").DisplayFormat.Interior.Color
    Range("D5").Interior.Color = Range("I5").DisplayFormat.Interior.Color
    Range("B6").Interior.Color = Range("G6").DisplayFormat.Interior.Color
    Range("C6").Interior.Color = Range("H6").DisplayFormat.Interior.Color
    Range("D6").Interior.Color = Range("I6").DisplayFormat.Interior.Color
End Sub

The first bit of the code means that the code will only run when something changes in the G4:I6 range. If you've got multiple ranges, I think you can put in multiple copies of this code, referring to different ranges, each of which will then only activate if those particular ranges are amended - but I'm not 100% sure about that...
I tried to do one line with Range("B4:D6") and Range("G4:I6") but for some reason that changed B4:D6 all to black fill - so I've had to put in one line for each cell.
There's probably someone on here who can come up with a more efficient way of doing this, as I'm not very good with VBA, but at least this works.

Here's your file with this macro in it: CF colour scale based on values in other range _ for johnharrison.xlsm
I've reset the fill to 'no fill' so that you can see it work - just change any cell in the right-hand table (typing the same value again will work) and you'll see the left-hand table colours change to match, as you want.

Hope that's of some help.

You're welcome and thanks for the rep.

Hi,

Just for good measure, here's a more compact version of that VBA code.

Sub Spread_Gradient()

    Dim row, col As Integer

    For row = 4 To 6
        
        For col = 2 To 4

            Cells(row, col).Interior.color = Cells(row, col + 5).DisplayFormat.Interior.color

        Next col

    Next row

End Sub

This macro has to be actually run to color the cells in, it doesn't do it automatically. I didn't even know you could do that. I bet someone more clever than I could merge both codes into a beautiful diamond of efficiency.

Inasmuch, please put code tags around you code before someone comes along and posts this obnoxious BS...

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)