Match cell color to range that uses conditional formatting color scales

Hi,

I am trying to match the interior cell colors in a range to another range that has cell colors set by conditional formatting using the color scale option. I am a relative beginner at VBA, but have been problem solving a few things with it mostly by searching the forums or google for "how to's". This one has me stumped though.

I've attached a sample doc, but a few parameters that have made this exercise difficult and deviate from most solutions I've found elsewhere:

• I am comparing actual values for each month (D6:P6) vs. the budget value for each month (R6:AD6) using color scales
• I am using AF6:AR6 to normalize what % of budget each month was and then have plugged in target value, max value and min value for the color scale in AT6:AV6
• In an ideal world I would just set the color scale rules to have the midpoint be the budget and then the maximum be a % of budget and minimum be a % of budget, but color scales don't allow for relational references so I'd have to do that individually for each cell
• I've tried to shortcut that by normalizing with % of budget and then just using a min %, 100% as midpoint, and max %
• The actual data set has many more rows, and some I will want to highlight smaller or larger ranges with min / max %'s, so will have to manually adjust the conditional formatting rules, but I think I can manage that to just a couple of groups
• I now want to use color scale that shows up on AF6:AR6 to fill in the interior cell colors of D6:P6 as that will be what shows up in the print range for the report ultimately
• I cannot just use the same conditional formatting in AF6:AR6 without manually adjusting each cell's conditional formatting rule

In all my searching, I've found VBA that works for changing cell colors for entire ranges to match another range, but does not work when the target range is conditional formatting. I've found the below code that works with conditional formatting, but only for a single cell, not an entire range.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.Range("D6").Interior.Color = Me.Range("AF6").DisplayFormat.Interior.Color
End Sub

Does anyone know a way to make this work? Is there VBA to match the entire range cell color to the target range cell color? Or a way to use VBA to get around the relative reference limitation with color scales? Any help would be greatly appreciated!



Thanks!

Thank you - I've updated my profile. I am using Microsoft 365 Apps for enterprise and am on a PC.