Feb 08 2022 05:10 PM
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...
Feb 09 2022 03:15 AM
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.
Feb 09 2022 10:14 AM
Thanks, I tried both, but neither changed anything in Column B.
I saved as xlsm, enabled macros, etc.
Feb 09 2022 10:27 AM
Strange. Here is my sample workbook. Does it work for you?
Feb 09 2022 10:45 AM
Feb 09 2022 10:49 AM
Feb 09 2022 11:46 AM
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.