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

Copper Contributor

I'm searching for used cars and have the make/model in Column B. I have the horsepower rating in Column H. Column H is conditionally formatted using gradient color scales to show most power in green, least in red. I would like this color scheme duplicated in the Column B based on results in Column H.

So, the words "Fiat 124 Spider" in B2 would show as red because H2=160 and "Jaguar XK R" in B17 would show as green because H17=420.

I can't find anything online that tells me how to make this happen...

Excel formatting.jpg

6 Replies

@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.

@Hans Vogelaar 

Thanks, I tried both, but neither changed anything in Column B.

I saved as xlsm, enabled macros, etc.

@NateW321 

Strange. Here is my sample workbook. Does it work for you?

Yes, it does. I prefer the code that automatically updates column b though.
This has, however, exposed my lack of knowledge on macros. When I put the code in my spreadsheet you used in your example, I didn't have a button show up on the sheet...
I did hit "run" from the code viewer though after saving/reopening.

@NateW321 

I created the button manually, from the Developer tab of the ribbon.

You don't really need it in your workbook - the macro needs to be run only once.

The other code, that should go into the worksheet module (right click sheet tab > select 'View Code') will update the colors automatically when you change values in column H.

One caveat: it won't work if the HP values in column H are formulas.