+ Reply to Thread
Results 1 to 3 of 3

Match cell color to range that uses conditional formatting color scales

  1. #1
    Registered User
    Join Date
    11-21-2011
    Location
    Stamford, CT
    MS-Off Ver
    Microsoft 365 Apps for enterprise
    Posts
    8

    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!
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,533

    Re: Match cell color to range that uses conditional formatting color scales

    Administrative Note:

    Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Registered User
    Join Date
    11-21-2011
    Location
    Stamford, CT
    MS-Off Ver
    Microsoft 365 Apps for enterprise
    Posts
    8

    Re: Match cell color to range that uses conditional formatting color scales

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Conditional Formatting Color Scales by Category
    By k64 in forum Excel General
    Replies: 1
    Last Post: 07-21-2016, 01:11 PM
  2. [SOLVED] conditional formatting -- change direction of evaluation -- using color scales (2010)
    By macrorookie in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-01-2014, 07:23 PM
  3. Copy Conditional Formatting Color Scales Excel 2007
    By catbert22 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-19-2014, 07:05 PM
  4. Replies: 0
    Last Post: 02-05-2014, 06:16 AM
  5. Replies: 4
    Last Post: 08-25-2012, 11:41 AM
  6. Conditional Formatting with Color Scales - Entire Row
    By PinkNinja in forum Excel General
    Replies: 1
    Last Post: 01-21-2011, 10:09 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1