Conditional formatting between rows with different values (words vs numbers)

NateW321 

Color scale type conditional formatting can only be applied to the range with the values.

Here is a workaround.

1) A macro to run only once that will color column B the same as column H.

Sub ColorB()
    Dim MyRange As Range
    Dim MyCell As Range
    Set MyRange = Range("H2:H18")
    For Each MyCell In MyRange
        MyCell.Offset(0, -6).Interior.Color = MyCell.DisplayFormat.Interior.Color
    Next MyCell
End Sub

2) Event code to update the coloring if you change a value in column H.

Right-click the sheet tab.

Select 'View Code' from the context menu.

Copy the following code into the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim MyRange As Range
    Dim MyCell As Range
    ' Change as needed
    Set MyRange = Range("H2:H18")
    If Not Intersect(MyRange, Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        For Each MyCell In Intersect(MyRange, Target)
            MyCell.Offset(0, -6).Interior.Color = MyCell.DisplayFormat.Interior.Color
        Next MyCell
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

3) Switch back to Excel.

Save the workbook as a macro-enabled workbook (.xlsm).

Make sure that you allow macros when you open it.