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

%3CLINGO-SUB%20id%3D%22%5C%26quot%3Blingo-sub-3140633%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3EConditional%20formatting%20between%20rows%20with%20different%20values%20(words%20vs%20numbers)%26lt%3B%5C%2Flingo-sub%26gt%3B%3CLINGO-BODY%20id%3D%22%5C%26quot%3Blingo-body-3140633%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CP%3EI'm%20searching%20for%20used%20cars%20and%20have%20the%20make%2Fmodel%20in%20Column%20B.%20I%20have%20the%20horsepower%20rating%20in%20Column%20H.%20Column%20H%20is%20conditionally%20formatted%20using%20gradient%20color%20scales%20to%20show%20most%20power%20in%20green%2C%20least%20in%20red.%20I%20would%20like%20this%20color%20scheme%20duplicated%20in%20the%20Column%20B%20based%20on%20results%20in%20Column%20H.%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3ESo%2C%20the%20words%20%22Fiat%20124%20Spider%22%20in%20B2%20would%20show%20as%20red%20because%20H2%3D160%20and%20%22Jaguar%20XK%20R%22%20in%20B17%20would%20show%20as%20green%20because%20H17%3D420.%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EI%20can't%20find%20anything%20online%20that%20tells%20me%20how%20to%20make%20this%20happen...%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%5C%26quot%3Blia-inline-image-display-wrapper%22%20lia-image-align-inline%3D%22%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Fgxcuf89792%2F%5C%26quot%3Bhttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F346490iAA092F1D09C1B195%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%5C%26quot%3B%22%20role%3D%22%5C%26quot%3Bbutton%5C%26quot%3B%22%20title%3D%22Excel%20formatting.jpg%22%20formatting.jpg%3D%22%22%20alt%3D%22Excel%20formatting.jpg%22%20%2F%3E%26lt%3B%5C%2Fspan%26gt%3B%26lt%3B%5C%2FP%26gt%3B%26lt%3B%5C%2Flingo-body%26gt%3B%3CLINGO-LABS%20id%3D%22%5C%26quot%3Blingo-labs-3140633%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CLINGO-LABEL%3EExcel%26lt%3B%5C%2Flingo-label%26gt%3B%26lt%3B%5C%2Flingo-labs%26gt%3B%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E
New 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.