Conditional Formatting Color Scales Based on Other Cells

Conditional Formatting Color Scales Based on Other Cells

With formula-based conditional formatting, it’s pretty easy to base the formats on other cells in the workbook, simply by referring to those cells in the formula. However, it’s more complicated if you want color scales derived from values in another range. In this post I discuss two ways to base color scales on another range. The first uses the camera tool while the second is a VBA subroutine that mimics conditional formatting.

Below is an example of what I’m talking about. The color formatting isn’t based on the values in the pivot table. Instead, it reflects the values in the second table, each cell of which contains the difference from the previous year in the pivot table. The colors range from red at the low end to green at the high end:

ormat with camera tool 1

So, here’s my two approaches to doing this:

Using the Camera Tool

This method uses Excel’s under-publicized camera tool, which creates a live picture linked to a group of cells. In this case the formatting is applied to a pivot table, but you can do it with any range. Here’s the steps:

  • Create the range of formulas that you’ll base the conditional formatting on.
  • Format the numbers in that range to be invisible, by using a custom format of “;;;”. All you want to see is the conditional formatting.
  • Use the camera tool to take a picture of the entire pivot table and paste it over the range you just created, lining up the conditionally formatted cells. Set the picture to be completely transparent, using the “no fill” setting. This way you can see through the picture to the conditionally formatted cells underneath.

The result will be like the illustration below. The source pivot table is in rows 11 to 18, and you can see that the picture starting in row 2 is linked to it. The cells underneath the picture contain the formulas referring to the pivot table. The conditional formatting is based on these cells, whose text is invisible because of the custom format.

format with camera tool 2

One thing to be aware of is that the picture doesn’t update until there’s a worksheet recalculation. You may have to force recalculation with F9 to have the picture update.

For one project I augmented this method by writing code that let me toggle back and forth between the values in the pivot table and the values the conditional formatting is based on.

Using VBA to Create “FauxMatting”

As the heading implies, this method attempts to replicate conditional formatting using VBA. The following subroutine takes two ranges – a source and a target range – as its arguments. It finds the highest and lowest values in the source range. It assigns each of those values a color in a scale from green to red, with white in the middle. This is done by dividing the range of values source values into 255 increments. The colors are then assigned to the target range:

Sub ConditionalFauxmatting(rngSource As Excel.Range, rngTarget As Excel.Range)
Const NUMBER_OF_INCREMENTS As Long = 255
Dim MinValue As Double
Dim MaxValue As Double
Dim ScaleIncrement As Double
Dim ScalePosition As Long
Dim var As Variant
Dim CellColor() As Long
Dim i As Long, j As Long

If Not (rngSource.Rows.Count = rngTarget.Rows.Count And rngSource.Columns.Count = rngTarget.Columns.Count) Then
    MsgBox "Source and Target ranges must be" & vbCrLf & "same shape and size"
    GoTo exit_point
End If
MinValue = Application.WorksheetFunction.Min(rngSource.Value)
MaxValue = Application.WorksheetFunction.Max(rngSource.Value)
'divide the range between Min and Max values into 255 increments
ScaleIncrement = (MaxValue - MinValue) / NUMBER_OF_INCREMENTS
'if all source cells have the same value or there's only one
If ScaleIncrement = 0 Or rngSource.Cells.Count = 1 Then
    rngTarget.Cells.Interior.Color = RGB(255, 255, 255)
    GoTo exit_point
End If
'assign all the values to variant array
var = rngSource.Value
ReDim CellColor(UBound(var, 1), UBound(var, 2))
For i = LBound(var, 1) To UBound(var, 1)
    For j = LBound(var, 2) To UBound(var, 2)
        'the scale position must be a value between 0 and 255
        ScalePosition = (var(i, j) - MinValue) * (1 / ScaleIncrement)
        'this formula goes from blue to red, hitting white - RGB(255,255,255) at the midpoint
        CellColor(i, j) = RGB(Application.WorksheetFunction.Min(ScalePosition * 2, 255), _
        IIf(ScalePosition < 127, 255, Abs(ScalePosition - 255) * 2), _
        IIf(ScalePosition < 127, ScalePosition * 2, Abs(ScalePosition - 255) * 2))
    Next j
Next i
'assign the colors stored in the array
'to the target range
With rngTarget
    For i = 1 To .Rows.Count
        For j = 1 To .Columns.Count
            .Cells(i, j).Interior.Color = CellColor(i, j)
        Next j
    Next i
End With

exit_point:
End Sub

The result looks like this:

format with VBA

I’m not sure how practical this is, but it was fun to figure out! Obviously, you’d want to tie this to a worksheet or pivot table event to update the formatting when the values change.

Here’s a workbook demonstrating these two methods.

8 thoughts on “Conditional Formatting Color Scales Based on Other Cells

  1. That Camera Tool trick is ingenious!

    I guess both these methods have practical application in the case that you want to view a dataset while overlaying some other metric of interest that is derived from that dataset. For instance, view Net Profit figures while also coloring them based on what Gross Profit was.

    Way cool!

  2. Thanks Jeff. One of my favorite uses of the camera tool was posted recently on the Spreadsheet Journalism blog.

    One other thing I should have mentioned is the problem that the camera tool can cause with flickering. Jan Karel Pieterse has a great tip to fix that on DDOE. I’ve used it with great success, even tying it to a WorkSheet_Activate event so that the picture only updates when you’re on the sheet that contains it.

  3. Great subroutine, works like a charm.

    I was wondering though, how do you change the colours in the scale? I would like to have yellow as the middle colour rather than white for instance.

  4. Hi Doug

    I have a similar albeit slightly more complex problem that I think the VBA version of the solution would be more suited to. Since my VBA coding/editing skills aren’t something to be proud of, your help would be greatly appreciated.

    1. Is it not simply possible to use a macro to copy formats from one range to also apply to another. So in your example you would use excel’s built in colour scales to create the original conditional formatting (Source Range) and then use the VBA sub to clone that formatting onto the Target Range.

    2. If the above is not possible then how would I edit the code so that it applies to multiple source and target ranges. In my case I am applying formatting on a line by line basis (in view you would see your Closing Stock number for that line item and I want the formatting to be based on Stock on Hand days, for example, which is calculated somewhere else) spread across different zones (columns).

    3. Is it then possible to change the Source Range via a dropdown menu so that you are able to format the target range based on different criteria (all precalculated but somewhere else in the workbook). Just a note: all calculated areas (source ranges) have exactly the same size as the single target range.

    Look forward to hearing from you,

    Gerhard

      • Hi again Doug

        Thank-you for your guidance thus far. I suppose they have led me to better understand my own problem.

        I’ve been playing around with your worksheets and seeing how they work. I need something a little different:

        Your sheets copy the conditional formatting applied to a source range to other target ranges. I need something that will only copy the colour fill applied to the source by the conditional formatting to a target range. i.e. Cell colours in the target range are effectively conditionally formatted by another set of data, in effect giving you two (or more) pieces of information in one cell.

        Coming back to my example about Closing Stocks and Stock on Hand Days. A high closing stock is not necessarily a problem if your stock turnover rate is acceptable. So in view you will see your closing stock numbers but their colour fill is determined by conditional formatting done on other cells (the stock on hand days) thereby alerting you which stock levels are actually an issue.

        Formatting line by line is not such a big problem. I only have about 600 lines so it’s not such a big deal to apply the formatting I want to one line and then once off copying them 600 times.

        I currently have my sheet set up so that there are 2 blocks of data on the front page. Each gives output based on seperate dropdowns for each. I basicaly want the fill colours applied to the block on the right (source) by conditional formatting to be applied to the block on the left (target). Apply and Clear Buttons will be great too.

        Busy trying to get it to work with the “Paste Special Plus” tool found here:

        http://www.andrewsexceltips.net/my_tips.htm

        Will post again if I get it all working.

        Gerhard

  5. Hi Doug,

    I have been using the VBA for the conditional formatting and it is extremely useful. I’m not hugely experienced with VBA and I wondered how easy it would be for you to edit the code for the following need.

    What I ideally need is conditional formatting that formats all positive values from white to green with the max value being the most intense green. And all negative values from white to red with the minimum value having the most intense red.

    Any help would be greatly appreciated.

    Best wishes,
    Scott

Speak Your Mind

Your email address will not be published. Required fields are marked *

To post code, do this: <code> your vba here </code>