VBA Conditional Formatting of Charts by Value

I’ve got a couple of tutorial pages on my web site that deal with conditionally formatted charts. The trick is to set up ranges containing formulas for each of the conditions, so that if the condition for that range is met, the range contains the value to plot, otherwise it contains #N/A, which isn’t plotted in a line or XY chart, or just a blank or zero, which is plotted as a zero-thickness bar or column in a bar or column chart. You can read about them here:

Simple Conditional Chart Example 1Simple Conditional Chart Example 2

As with conditional formatting in the worksheet, the formatting you can achieve is limited only by your ability to construct formulas. Of course, in the chart, you are not limited to three conditional formats as you are in the worksheet (in Excel 2003 at least; the conditional formatting enhancements in Excel 2007 are mind-boggling).

Although I prefer to use the formulaic approach above, sometimes it makes more sense to use VBA to apply formats. Suppose you have a column chart, and the values may be in any order. However, you want to color a column according to its value, so that small values always are colored red and large values are always green.

The following protocol allows you to color the points in a series according to colors you’ve reserved for certain values. The range below illustrates the data: range A1:A4 contains a list of the categories, with each cell filled with the desired color for that value in a chart; actually, the procedure will use the color in the cell with the smallest value greater than or equal to the point’s value. A6:B10 contains data for Chart 1, and A12:B16 contains data for Chart 2. (The colors in A1:A4 were defined using the Color Brewer utility described in my earlier post, Using Colors in Excel Charts.)

The charts made from the two ranges are not remarkable.


There is a simple VBA procedure that will apply the colors from the designated range to the appropriate points, based on their categories.

Excel 2003 and earlier:

Sub ColorByValue()
  Dim rPatterns As Range
  Dim iPattern As Long
  Dim vPatterns As Variant
  Dim iPoint As Long
  Dim vValues As Variant
  Dim rValue As Range

  Set rPatterns = ActiveSheet.Range("A1:A4")
  vPatterns = rPatterns.Value
  With ActiveChart.SeriesCollection(1)
    vValues = .Values
    For iPoint = 1 To UBound(vValues)
      For iPattern = 1 To UBound(vPatterns)
        If vValues(iPoint) <= vPatterns(iPattern, 1) Then
          .Points(iPoint).Interior.ColorIndex = _
              rPatterns.Cells(iPattern, 1).Interior.ColorIndex
          Exit For
        End If
      Next
    Next
  End With
End Sub

Excel 2007 and later:

Sub ColorByValue()
  Dim rPatterns As Range
  Dim iPattern As Long
  Dim vPatterns As Variant
  Dim iPoint As Long
  Dim vValues As Variant
  Dim rValue As Range

  Set rPatterns = ActiveSheet.Range("A1:A4")
  vPatterns = rPatterns.Value
  With ActiveChart.SeriesCollection(1)
    vValues = .Values
    For iPoint = 1 To UBound(vValues)
      For iPattern = 1 To UBound(vPatterns)
        If vValues(iPoint) <= vPatterns(iPattern, 1) Then
          .Points(iPoint).Format.Fill.ForeColor.RGB = _
              rPatterns.Cells(iPattern, 1).Interior.Color
          Exit For
        End If
      Next
    Next
  End With
End Sub

Select a chart, and run the procedure above (press Alt+F8 to bring up the macro dialog, select the procedure, and click Run). The bars will change color according to their values and the colored table in A1:A4.


To format by category label, see VBA Conditional Formatting of Charts by Category Label.
To format by series name, see VBA Conditional Formatting of Charts by Series Name.

 

Peltier Tech Charts for Excel

Comments

  1. I’m trying to create bar graphs where the bar (for each month of the year x axis) changes color (green above flat budget $/unit) or (red below flat budget $/unit). Does anyone know how to do this? I also have a another chart where the budget volume is different by month and would like the bar for actual volume by month to change to red or green depending if the actual volume is higher/lower than the budget volume for the month. Any suggestions would be appreciated.

  2. Valerie –

    If the green/red transition occurs at zero, you could try Invert if Negative. If the transition is at a non-zero value, which sounds like your case, you can try Simple Conditional Charts.

  3. Please refer to this image [image no longer available].

    Some points about the graph before the question. It’s critical for my purpose that each slice appear to be 1/8 of each ring (each represent an azimuth angle around an airplane). The trick was to set the value of each cell to “1” plus the actual value divided by 1000. That makes the difference between slices so minimal that the rings appear to be equally spaced. The hope is to then shade each slice as shown in range (I1:I4) which was the only change I made to the VBA subroutine

    I also rotated the data so the zero azimuth is pointed to the right, and X-1 is the inner ring.

    I’m using this routine with the referenced donut chart and it runs OK but only changes the colors of the inner ring (I think that’s because of the line “With ActiveChart.SeriesCollection(1)” which I don’t recall how to fix to include every SeriesCollection). But, the colors did not match the defined region (I1:I4) nor did they follow any pattern regarding the values.

    One thing that has me concerned is after I started I found in the Help file that this works for graphs such that “You don’t have more than seven categories per data series” which I do…but the chart was created successfully. Why this restriction? What does in impact (since I created one OK)?

    Help?

  4. Ross –

    You need another link to loop through each series:

    Sub ColorByValue()
      Dim rPatterns As Range
      Dim iPattern As Long
      Dim vPatterns As Variant
      Dim iPoint As Long
      Dim vValues As Variant
      Dim rValue As Range
      Dim srs As Series
    
      Set rPatterns = ActiveSheet.Range("A1:A4")
      vPatterns = rPatterns.Value
      For Each srs In ActiveChart.SeriesCollection
        With srs
          vValues = .Values
          For iPoint = 1 To UBound(vValues)
            For iPattern = 1 To UBound(vPatterns)
              If vValues(iPoint) <= vPatterns(iPattern, 1) Then
                .Points(iPoint).Interior.ColorIndex = _
                    rPatterns.Cells(iPattern, 1).Interior.ColorIndex
                Exit For
              End If
            Next
          Next
        End With
      Next
    End Sub

    If the color index commands don’t work, you could try replacing .ColorIndex by .Color (color index worked for me).

  5. Thanks, I figured that would do it but while I’m good at reading code, I’m not so good at creating it!

    As for the colors, It’s only the A4 green that doesn’t come out correctly. So I created a green default template chart and ingore the forth loop (if it belongs green, it won’t change it). Reduces run time by 25% also :)

  6. That could be made faster by turning off screen updating while it works:

    Sub ColorByValue()
      Dim rPatterns As Range
      Dim iPattern As Long
      Dim vPatterns As Variant
      Dim iPoint As Long
      Dim vValues As Variant
      Dim rValue As Range
      Dim srs As Series
    
      Application.ScreenUpdating = False
      Set rPatterns = ActiveSheet.Range("A1:A4")
      vPatterns = rPatterns.Value
      For Each srs In ActiveChart.SeriesCollection
        With srs
          vValues = .Values
          For iPoint = 1 To UBound(vValues)
            For iPattern = 1 To UBound(vPatterns)
              If vValues(iPoint) <= vPatterns(iPattern, 1) Then
                .Points(iPoint).Interior.ColorIndex = _
                    rPatterns.Cells(iPattern, 1).Interior.ColorIndex
                Exit For
              End If
            Next
          Next
        End With
      Next
      Application.ScreenUpdating = True
    End Sub
  7. Hi there,

    I want to modify the conditional formatting code by changing teh colour of the graphs not by the value of the bar but by another field.

    The values each bar should be coloured is located on another worksheet (Worksheet X) in column N. My question is how do i modify the below code to not take graph values as the colour but take the values in column N on worksheet X.

    Sub ColorbyValue()
    Dim rPatterns As Range
    Dim iPattern As Long
    Dim vPatterns As Variant
    Dim iPoint As Long
    Dim vValues As Variant
    Dim rValue As Range
    Dim srs As Series

    Application.ScreenUpdating = False
    Set rPatterns = ActiveSheet.Range(“A1:A6”)
    vPatterns = rPatterns.Value

    For Each srs In ActiveChart.SeriesCollection
    With srs
    vValues = .Values
    For iPoint = 1 To UBound(vValues)
    For iPattern = 1 To UBound(vPatterns)
    On Error Resume Next
    If vValues(iPoint) <= vPatterns(iPattern, 1) Then
    .Points(iPoint).Interior.ColorIndex = _
    rPatterns.Cells(iPattern, 1).Interior.ColorIndex
    On Error GoTo 0
    Exit For
    End If
    Next
    Next
    End With
    Next
    Application.ScreenUpdating = True
    End Sub

  8. Change the vValues definition to:

        vValues = Worksheets("X").Range("N1:N100").Value

     
    Adjust the address N1:N100 to the range with the values of interest.

  9. Hi Jon!

    Thanks mate, but the code only seems to colour the graphs with the first colour and ignores all other values with different associated colours. So if my values are

    1 = red
    2 = yellow
    3 = green

    The code colours all bars red, even if they have a value of 2 or 3!

  10. Jason –

    Is the range address in the vValues = statement correct?
    Are the values in that range numerical? Text is interpreted as zero.

    Did you run this with no error? I should have changed another line:

        If vValues(iPoint, 1) < = vPatterns(iPattern, 1) Then

     

  11. oh thanks mate!! that other line ammendment fixed it. really appreciate your help buddy!

  12. Hi Jon,

    I am looking to color data points based upon the quadrant in which they are present (therefore this is an xy scatter) with the intercept at the average value of x, so therefore the colour will be bases upon 1. a +ve or -ve number on one axis, and 2. the presence above or below the mean of the x axis, any help would be most appreciated

    Dean

  13. Dean –

    Good question. I’ve answered it in a new post, Conditional XY Charts Without VBA.

  14. Thanks for the great info you have on this site, I found some very interesting stuff on here. Sure I will use some of it at some point.

    Regards

    Shane

  15. i’m trying to get a button set up so I don’t have to go through the developer tab each time and run the macro, but when I try to do this, i get an error for Object Variable or With block variable not set, so it’s like i’m not clicking on the chart that I want to update. Is there a way that can set up my VBA so that I don’t have to be clicked inside of the chart?

  16. Tyler –

    If there’s no other chart on the sheet, try the code below. If there are multiple charts, how would the program know which one to change?

    Sub ColorByValue()
      Dim rPatterns As Range
      Dim iPattern As Long
      Dim vPatterns As Variant
      Dim iPoint As Long
      Dim vValues As Variant
      Dim rValue As Range
    
      Set rPatterns = ActiveSheet.Range("A1:A4")
      vPatterns = rPatterns.Value
      With ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)
        vValues = .Values
        For iPoint = 1 To UBound(vValues)
          For iPattern = 1 To UBound(vPatterns)
            If vValues(iPoint) <= vPatterns(iPattern, 1) Then
              .Points(iPoint).Interior.ColorIndex = _
                  rPatterns.Cells(iPattern, 1).Interior.ColorIndex
              Exit For
            End If
          Next
        Next
      End With
    End Sub
  17. Hi Jon,

    I have a question regarding conditional formatting bar charts. I’ve tried your examples, but I don’t get them to work for me. I Hope you can help.

    I have a series of bar charts that I want to change from red to green if they are greater than / equal to or less than a certain value. I have uploaded a picture to show you the setup:

    [image no longer available]

    What I want to do is change the color to red if the value is less than the norm. Green if the value is equal to or greater than the norm.

    This graph is different from the setup in your examples for the simple conditional format and I have tried a lot of things but I think I don’t have enough experience to get this to work without any help.

    Thanks for your answer!

  18. I’m sorry but I just found out that I did not set the colors right in the example picture. The second bars should be green, red, red, green and red.

  19. Vincent –

    The examples here are to provide insight into concepts, but often the implementation needs a different algorithm.

    In this article, the algorithm simply loops through the values of every point in the series, compares that value to a table, and applied the appropriate color to the corresponding data point.

    Your situation needs a different approach:
    A point is labeled only if it is the value for a product, not for a norm. This means if the point has a zero value, don’t label it, because it’s not a product’s point, and if the previous point has zero value, don’t label this point either because the previous point isn’t a norm. If it’s okay to label the point, compare its value to the previous value, and apply either green or red fill.

    Something like this:

    Sub DoColor()
      Dim vPatterns As Variant
      Dim iPoint As Long
      Dim vValues As Variant
      Dim rValue As Range
    
      With ActiveChart.SeriesCollection(1)
        vValues = .Values
        For iPoint = 2 To UBound(vValues)
          If vValues(iPoint) > 0 And vValues(iPoint - 1) > 0 Then
            If vValues(iPoint) >= vValues(iPoint - 1) Then
              .Points(iPoint).Interior.Color = RGB(0, 255, 0)
            Else
              .Points(iPoint).Interior.Color = RGB(255, 0, 0)
            End If
          End If
        Next
      End With
    End Sub

    I’m not going to try here to reproduce your gradient fill. You can record a couple macros in 2003 to get the additional syntax you need for that.

  20. Hi Jon,

    Thank you for your quick answer! I will get into VBA a little more (I am a novice) as I understand that none of the simple conditional formats will work for my situation.

    Maybe I will just get rid of the gradients to make things easier.

    If I don’t get it to work I may come back and ask you a question, but not before I gain knowledge about VBA.

    (Updated picture: [image no longer available])

  21. Hi Jon,

    Thanks again for your help. I read a lot about VBA last week and I’m beginning to understand how it works now. For a novice like me it is quite hard understanding the object-model. I don’t know where to find good information to start programming with VBA, but with al little Google and some code from others (and a lot from you) I made this working macro for all the charts on the active worksheet. I even managed to assign it to a button on the page. It works great! :-)

    Sub AdjustChartColor()
    ‘ Macro created on 2010-04-19 by Vincent
    ‘ with help from Jon Peltier: http://www.peltiertech.com

    Dim cObject As ChartObject
    Dim iPoint As Long
    Dim vValues As Variant

    For Each cObject In ActiveSheet.ChartObjects
    With cObject.Chart.SeriesCollection(1)
    vValues = .Values
    For iPoint = 2 To UBound(vValues)
    If vValues(iPoint) > 0 And vValues(iPoint – 1) > 0 Then
    If vValues(iPoint) >= vValues(iPoint – 1) Then
    .Points(iPoint).Fill.ForeColor.SchemeColor = 4
    Else
    .Points(iPoint).Fill.ForeColor.SchemeColor = 3
    End If
    End If
    Next
    End With
    Next
    End Sub

  22. Hi Jon,

    Thank you for this brilliant site of yours!
    I’m trying to use the proposed code above but with some slight changes:
    1) I want to change the color of the bars (datapoints) depending on the name of the point (xvalue) and not the value
    2) I want to the bar to get the same color as the cell I’m pointing at in the VBA code (ie. the vValue = range)
    3) the Cells I’m pointing at are formated through conditional formatting.

    I believe I have managed to get 1) and 2) to work but it only changes the bar colors to white (none color)…?! I think this might be because of the conditional formatting.

    Any advise?
    Thanks for your answer!

    Regards,
    Leo

  23. To be more precise I use the following code.
    Sub Color_change()

    Dim serX As Series
    Dim vntNames As Variant
    Dim lngPoint As Long
    Dim lngIndex As Long
    Dim rPatterns As Range
    Dim vPatterns As Variant

    Set rPatterns = Worksheets(“Graph input”).Range(“E21:E50”)
    vPatterns = rPatterns.Value
    With ActiveChart.SeriesCollection(1)
    vntNames = .XValues
    For lngIndex = LBound(vntNames) To UBound(vntNames)
    lngPoint = lngPoint + 1
    .Points(lngPoint).Interior.ColorIndex = rPatterns.Cells(lngPoint, 1).Interior.ColorIndex
    Next
    End With
    End Sub

    It works fine if I manually set the background color in the cells but not if they are conditionally formated

  24. Leo –

    It’s not easy to apply a cell’s conditional formatting to a chart element. The cell retains its original formatting, with the conditional formatting overruling it. When you query the format of the cell, you only see the original format. You need first to find out which condition is met, then find out what the applicable format is for that format. This is the format to apply to the points.

  25. Hi Jon,

    Thanks a lot for you comments.

    I have tried to do a work around bu can’t manage getting it to work.
    Basically what I’m trying to do is to change the colors of each data point in a bar chart depending on its name (XValues).

    I have in the input sheet the following columns

    Name Type

    Volvo Car
    BMW Car
    Boening Airplane
    Zodiac Boat

    Type definition column:
    Car
    Boat
    Airplane

    I want the code to do the following (pseudo code):
    1) compare each data point in the bar chart with the name column
    2) if they are equal:
    3) compare the typ column with the typ_definition_column
    4) if they are equal
    5)set interior.color = RGB(x,y,z)

    I coded the following but can’t get it to work (Nothing happens).
    Could you possibly point me out into the right direction?

    Once again thans for a great site!

    /Leo

    Sub ColorByXValue()

    Dim rName As Range
    Dim iName As Long
    Dim vName As Variant

    Dim rGroup As Range
    Dim vGroup As Variant

    Dim iPoint As Long

    Dim vChrValues As Variant
    Dim rChrValue As Range

    Dim rGroupType As Range
    Dim vGroupType As Variant

    Set rName = Worksheets(“Graph input”).Range(“F21:F50”)
    vName = rName.Value

    Set rGroupType = Worksheets(“Graph input”).Range(“G52:F56”)
    vGroupType = rGroupType.Value

    Set rGroup = Worksheets(“Graph input”).Range(“H21:H50”)
    vGroup = rGroup.Value

    With ActiveChart.SeriesCollection(1)
    vChrValues = .XValues
    For iPoint = LBound(vChrValues) To UBound(vChrValues)
    For iName = 1 To UBound(vName)
    If vChrValues(iPoint) = vName(iName, 1) Then
    If vGroup(iName, 1) = vGroupType(1, 1) Then
    .Points(iPoint).Interior.Color = RGB(56, 46, 25)
    ElseIf vGroup(iName, 1) = vGroupType(2, 1) Then
    .Points(iPoint).Interior.Color = RGB(113, 93, 50)
    ElseIf vGroup(iName, 1) = vGroupType(3, 1) Then
    .Points(iPoint).Interior.Color = RGB(204, 191, 142)
    ElseIf vGroup(iName, 1) = vGroupType(4, 1) Then
    .Points(iPoint).Interior.Color = RGB(229, 219, 186)
    End If
    End If
    Next
    Next
    End With
    End Sub

  26. Leo –

    Step through the code, and make sure each of the arrays contains the data you expect it to have.

  27. Thanks Jon!

    After some struggling I finally got it to work.
    It seems as chart objects in Excel add an extra space after each data point name (XValues) i.e. if the name of the bar is “Volvo” the value is “Volvo “!

    Once again thanks for you help!
    /Leo

  28. Leo –

    This mismatch probably is more a function of where the data was extracted from. Excel doesn’t just arbitrarily append spaces to labels.

  29. Hi John,

    Greatly appreciate the posting of this code to set us on our way.

    I have been trying to apply it an example not markedly different to your sample but for the rPatterns is a horizontal – not vertical – range (say A1:D1). The macro is only changing the first datapoint. Stepping through the code in the Locals Window I notice that the UBound(vPatterns) value is 1 not 4. Do I need to amend the code for an Array orientation change? Also, does the rValue variable serve a purpose?

    Thank you,
    David

  30. David –

    Strictly speaking, the UBound command should have been

    For iPattern = 1 To UBound(vPatterns, 1)

    since it needed the number of rows, but by default, if you leave off the second parameter, VBA assumes 1. We need a few adjustments to query a horizontal range:

    For iPoint = 1 To UBound(vValues) 
      For iPattern = 1 To UBound(vPatterns, 2) 
        If vValues(iPoint) < = vPatterns(iPattern, 2) Then 
          .Points(iPoint).Interior.ColorIndex = _ 
              rPatterns.Cells(iPattern, 2).Interior.ColorIndex 
          Exit For 
        End If 
      Next 
    Next
  31. Jon,

    Greatly appreciate your explanation.

    Thank you,
    David

  32. Hi John,

    I have similar question about formatting the graph. If you would help me with that I would greatly appreciate it.

    I have a sheet that contains 3 columns and 20 rows. First column contains the text in each cell (company names). The second and third columns contain numbers related to the company name in the first column. I would like to assign each name its specific colour, so when I change the data, the VB recognizes the company name and changes the colour of the first and second points (which are in the 1st and 2nd columns) on the graph.

    Could you, please point me into the right direction?

    Thank you in advance!

  33. Thank you so much for these fantastic ideas.

    Sekiya-Nanny

  34. This has been a tremendous help! The only question I have is when I have two charts on the page it changes both of them – is there a way around this? (I’ve modified the stuff Vincent worked on with you).
    Sub AdjustChartColor()
    ‘ Macro created on 2010-04-19 by Vincent
    ‘ with help from Jon Peltier: http://www.peltiertech.com

    Dim cObject As ChartObject
    Dim iPoint As Long
    Dim vValues As Variant

    For Each cObject In ActiveSheet.ChartObjects
    With cObject.Chart.SeriesCollection(1)
    vValues = .Values
    For iPoint = 1 To UBound(vValues)
    If vValues(iPoint) > 50 Then
    .Points(iPoint).Fill.ForeColor.SchemeColor = 2
    Else
    .Points(iPoint).Fill.ForeColor.SchemeColor = 6
    End If
    Next
    End With
    Next
    End Sub

  35. Hi,
    I found the article of great help.

    However I’m having trouble with the Reset To Match Style command; once you have clicked over the chart then the VBA code does not affect the chart anymore.

    The only workaround is to select manually a series, bring up the Formatting popup and change anything in there – then again the code works but only for the series I changed something.

    I’m using Office 2010; any suggestion would be of a great help.

    thanks, Gjergji

  36. Gjergji –

    That’s strange, somehow the Reset to Match Style must lock the chart formatting. I’ve never used this feature, since there’s no style I particularly want to match.

    What are you using this for? Could you do the style matching manually, so the chart’s formatting isn’t locked?

  37. Hi John,
    there is no particular importance from the usage point of view.
    I came across it while experimenting formatting the chart with the VBA code and it was the only way I found to reset it to the initial state.

    I was wondering if there was a way to circumvent it.

    thanks, for your help and reply
    best regards, Gjergji

  38. Hi

    I am struggling with my pivot chart. Basically depending on the value I want the colour of the bar to change

    >= 95 – Green
    >=85 and < 95 – Orange
    95, <95 and < 85. All columns are setting to Green. Are the values in my range the problem?

    Many thanks

    Rachel

  39. Rachel –

    I think you’re better off using the approach in Conditional Formatting of Excel Charts. It doesn’t use code, and there’s no issues with the weirdness of the Excel 2007 color system.

  40. Hi,

    I am very new to VBA and this has been amazingly instructive & helpful!!

    I would love to be able to adapt the original code so that I can use a different range to assign the correct colour for each different chart point in my pie chart. I don’t know how to select each point individually and then move on to the next one, rather than loop through all of the points assessing them all against the same range. Can you help me?

    Thanks so much!!
    Katie

  41. Katie –

    You might be trying to squeeze more meaning into your pie chart than is possible to extract. But anyway, what you want is possible, though it takes a more complicated algorithm. Hang tight and I’ll write it up in a new post.

  42. Thanks Jon,

    What I am attempting to do is to automate a sort of supplier KPI “steering wheel”, so each segment of my pie chart, whilst sized according to weighting rather than value, is scored individually and against differing criteria. If I can get this working it will save hours and hours every month!!

    Many many thanks,
    Katie

  43. Hello,
    When I duplicate the example you have above, I am able to get the VBA to work..however when I try and use it on my spreadsheet, it is not working, and I think it has something to do with the fact that I have multiple series, as well as the chart in a different tab. Help?
    Thanks in advance,
    Annamaria

  44. Annamaria –

    You have to make some adjustments to the code. It’s probably easiest to select the chart on its sheet before running the code, so you need to specify the sheet that contains the formatted range. Also you need to cycle through the series in the chart.

    Sub ColorByValue() 
      Dim rPatterns As Range 
      Dim iPattern As Long 
      Dim vPatterns As Variant 
      Dim iSrs As Long
      Dim iPoint As Long 
      Dim vValues As Variant 
      Dim rValue As Range 
    
      '' Specify sheet containing the formatted range
      Set rPatterns = Worksheets("Formats").Range("A1:A4") 
      vPatterns = rPatterns.Value
    
      '' Cycle through all series in chart
      '' Alternatively identify intended series as, e.g.,
      '' SeriesCollection(5) or SeriesCollection("Series Name")
      For iSrs = 1 To ActiveChart.SeriesCollection.Count 
        With ActiveChart.SeriesCollection(iSrs) 
          vValues = .Values 
          For iPoint = 1 To UBound(vValues) 
            For iPattern = 1 To UBound(vPatterns) 
              If vValues(iPoint) < = vPatterns(iPattern, 1) Then 
                .Points(iPoint).Interior.ColorIndex = 
                    _ rPatterns.Cells(iPattern, 1).Interior.ColorIndex 
                Exit For 
              End If 
            Next 
          Next 
        End With 
      Next
    End Sub
  45. Thank you so much Jon!! This works perfectly.

  46. Jon,
    This code is fantastic, I’m using the version adapted for Ross, earlier in the comments (for multiple series). I am using this on a 3D column grid of around 60×100 cells. It works but is very slow and freezes excel for awhile, do you have any tips on how it could be sped up? I do have a number of blank cells, is there any way to make the macro skip these cells to make it faster.

    Any ideas much appreciated!

    Thanks again for your help.

  47. Matthew –

    Are you using the second version for Ross, with Application.ScreenUpdating changed to False at the beginning and back to True at the end?

  48. Matthew replied off-line:
    Yes, I’m using that version with screenupdating false then true. It still takes a couple of minutes to refresh and excel freezes in the meantime.
    If I reduce the grid size to half, it is noticeably quicker but still lags significantly. My PC is not particularly underpowered.
    Any ideas?

    My follow-up:
    Which version of Excel? How many datapoints do you have?
    2007 is particularly slow, especially with more than a couple hundred points.

  49. John,
    These articles your write are incredibly useful. Thank you very much for sharing your expertise.

    Just to build on this methodology, users can use this for colouring doughnut charts too. Whilst they may not be such good charts, management do seem to like them, especially for traffic light type colouring of key metrics.

    As an example, say you have one overall KPI – “customer satisfaction” and that KPI has 6 sub components. the VBA method coems in really handy if each sub component has a different weighting within the overall KPI, say;
    1. website experience is worth 50% of our overall customer sat KPI
    2. delivery time is worth 25% of the overall customer sat KPI
    3-6 same thing for the remaining 4 sub components so that you get to 100% in total

    What we want is a doghnut chart for the customer satisfaction KPI with each sub component highlighted in either red, green or amber depending on how actuals are v’s the targets we set for each of those sub components.

    In the data we plot the doughnut chart as per the percentages in items 1-6, so website experience is 0.5, delivery time 0.25 etc. that draws the doughnut with the subcomponents in the correct proportions.

    Also create three cells e.g. A1:A3 with your traffic light colouring in e.g. A1, enter cell value 1 and colour green, A2 enter cell value 2 and colour amber and A3 enter cell value 3 and colour red. This sets up your traffic lights.

    Now all we need is a formula in the column next to where we had the proportions data. All this formula does is compare our actual outturn with the targets set and if better than target inserts value 1, if under target inserts 3 and if within a set rnage around the target would insert 2. Those ranges are dependent upon the targets set by the organisation.

    Then using the VBA posted by Jason Peters and updated by you you can tell the doughnut chart to apply the formatting in the cells A1:A3 to the values you created in the formula cells and hey presto a chart in the right proportions and coloured in the traffic light style for your KPI.

  50. Chris –
    You mention multiple worst practices in your comment.
    1. Donut charts, for reasons we’ve been over.
    2. Red-Yellow-Green color scales, for color-related issues. What if you have to photocopy or fax the printout? Or, what if the viewer is among the >8% of men who have red-green color perception difficulties?
    3. Something as easy as pie or donut wedges, and even bar charts, can be set up to change color without code.

  51. John, I agree wholeheartedly with your comments around the structure of the charts. Unfortunately we sometimes have to play the hand we are dealt until we can get people to listen to our recommendations.

  52. Jon,
    Thank you for your very helpful articles. These seem to be addressing a problem very similar to the one I am trying to solve. I have several series of data types from an experiment that cycles through a loop. I then plot the data and each loop lies nearly on top of the first but the differences are important. I would like to colour code a scatter plot with smooth lines to show a different colour for each of the cycles. My data has a column with the cycle number in (1,2,3, etc) and then several more columns with voltage, current etc. so for example I would like to plot voltage against current in a scatter plot with smooth lines where the colour depends on the number in the cycle column. Since I am plotting the data several different ways separating the data out into different columns is possible but very cumbersome, any help with a VBA alternative would be greatly appreciated.

  53. Louise –
    You could use a pivot table to split the data into separate columns.
    You could use extra columns with formulas, as shown in Split Data Range into Multiple Chart Series without VBA.
    Or you could use VBA to Split Data Range into Multiple Chart Series.

  54. Jon,

    This is briliant! thankyou for taking the time to work out how to do this, it has saved me about 3 days work today.

    Martyn

  55. Jon, thank you this is a wonderful function.
    However, I have the same problem as Jason, Tuesday, November 24, 2009 at 10:55 am, where it only colors with the first value. This is because my values are text, as you mentioned, and not numerical, so the vValues = .Values does not work, but the Ubound function messes up if I try to just change the line to vValues = .XValues. I’m a beginner with vba, and I have tried, but I simply cannot figure out how to adapt this code to accept text values. Could you perhaps give me a hint?
    Katrine

  56. Katrine –
    Jason wanted to test a different range in the worksheet to apply his colors. The original code looks at
    series.Values
    which is the array of Y values. I had Jason look at
    range.Values
    which is the array of values from a worksheet range. You have mentioned
    series.XValues
    which is the array of X values in the chart series.
    What are the text values you want to evaluate? Certainly it’s not Y values, and I’m not sure it’s the X values. Is it a worksheet range, as in Jason’s case?
    Note that UBound will break if its argument is not an array.

  57. Hi Jon,
    Thank you so much for this site – it’s been a huge help to me.
    I’ve inherited a lot of workbooks with a lot of VBA in them, mostly to format data points in charts. The issue is that we are just now moving from Excel 2003 to 2010 and not all of this code works. It’s fine if there is only one data series per chart, and if there is no pattern, but I am increasingly needing to show 3 series per chart which necessitates the using of patterns as well as different fill colors. What I was able to do in 2003 was set the series’ default to one fill color and a black pattern foreground; while the fill color would change, the pattern remained black. Now the fill color changes, but so does the foreground color of the pattern.

    The following is what I have successfully been using in 2003; can you provide some insight as to what I need to change?

    Thanks so much,
    Ursula

    Sub FormatInitAccBHS()

    Dim x As Integer
    Dim val As Variant
    Dim MLT As Range
    Dim Tar As Variant
    Dim Rxr As Range
    Dim PhD As Range

    Set MLT = Sheets(“Data”).Range(“C223:N223”)
    Set Tar = Sheets(“Data”).Range(“X12”)
    Set Rxr = Sheets(“Data”).Range(“C222:N222”)
    Set PhD = Sheets(“Data”).Range(“C224:N224”)

    ActiveSheet.ChartObjects(“Chart 93”).Select

    With ActiveChart.SeriesCollection(“MLT”)
    val = .Values
    For x = 1 To .Points.Count
    If val(x) >= Tar Then
    .Points(x).Interior.ColorIndex = 43
    ElseIf val(x) = Tar Then
    .Points(x).Interior.ColorIndex = 10
    ElseIf val(x) = Tar Then
    .Points(x).Interior.ColorIndex = 31
    ElseIf val(x) < Tar Then
    .Points(x).Interior.ColorIndex = 18
    End If
    Next x

    End With

    End Sub

  58. The previous post didn’t include all the code:

    Sub FormatInitAccBHS()

    Dim x As Integer
    Dim val As Variant
    Dim MLT As Range
    Dim Tar As Variant
    Dim Rxr As Range
    Dim PhD As Range

    Set MLT = Sheets(“Data”).Range(“C223:N223”)
    Set Tar = Sheets(“Data”).Range(“X12”)
    Set Rxr = Sheets(“Data”).Range(“C222:N222”)
    Set PhD = Sheets(“Data”).Range(“C224:N224”)

    ActiveSheet.ChartObjects(“Chart 93”).Select

    With ActiveChart.SeriesCollection(“MLT”)
    val = .Values
    For x = 1 To .Points.Count
    If val(x) >= Tar Then
    .Points(x).Interior.ColorIndex = 43
    ElseIf val(x) = Tar Then
    .Points(x).Interior.ColorIndex = 10
    ElseIf val(x) = Tar Then
    .Points(x).Interior.ColorIndex = 31
    ElseIf val(x) < Tar Then
    .Points(x).Interior.ColorIndex = 18
    End If
    Next x

    End With

    End Sub

  59. Ursula –
    I don’t know offhand what you need to change to make the code work. If you step through the code in the VB Editor (using the F8 key to advance line by line) does execution flow through the If/Else/Then block as expected? There seem to be two branches for val(x) = Tar, so only the first of these will ever be followed. Also, Tar is set to a range, so your code should compare val(x) to Tar.Value instead of Tar (thought VBA usually guesses correctly what you meant).
    The bigger issue might be the use of ColorIndex, which was made obsolete in Excel 2007. The new syntax looks like this:
    .Points(x).Format.Fill.ForeColor.RGB = RGB(255, 0, 255)
    You’ll have to determine the desired RGB values.

  60. Hello,

    Could you please post a modified version of the original 2007 code that loops through all charts in a workbook. I get it to work with one chart but I have a couple hundred I frequently update.

    Thanks

  61. Kyle –
    I would first extract the working part of the routine and place it into a function

    Function ColorByValue(cht As Chart)
      Dim rPatterns As Range
      Dim iPattern As Long
      Dim vPatterns As Variant
      Dim iPoint As Long
      Dim vValues As Variant
      Dim rValue As Range
    
      Set rPatterns = ActiveSheet.Range("A1:A4") ' specify sheet if needed
      vPatterns = rPatterns.Value
      With cht.SeriesCollection(1)
        vValues = .Values
        For iPoint = 1 To UBound(vValues)
          For iPattern = 1 To UBound(vPatterns)
            If vValues(iPoint) < = vPatterns(iPattern, 1) Then
              .Points(iPoint).Format.Fill.ForeColor.RGB = _
                  rPatterns.Cells(iPattern, 1).Interior.Color
              Exit For
            End If
          Next
        Next
      End With
    End Function

    Then I would write a sub to loop through all the charts.

    Sub ColorAllCharts()
      Dim sh As Object ' Sheet
      Dim chob As ChartObject
      Dim cht As Chart
    
      ' get all chart sheets
      For Each cht In ActiveWorkbook.Charts
        ColorByValue cht
      Next
    
      ' get all embedded charts on all sheets
      For Each sh In ActiveWorkbook.Sheets
        For Each chob In sh.ChartObjects
          ColorByValue chob.Chart
        Next
      Next
    End Sub
  62. Hi, John

    I’m trying to get a similar approach but specifying ranges of values. For instance:

    Color in green values greater than 5

    Color in red values lesser than 5

    Color in grey values between -5 and 5.

    How can I do that?

    Thanks ;)

  63. Miguel –

    A1 would be red and contain the value -5, A2 would be gray and contain the value 5, and cell A3 would be green and contain a very large number (like 1000). Change the address of rPatterns to “A1:A3”. Select the chart and run the code.

  64. Works great, Jon

    Thanks so much ;)

  65. Hi! I tried your example for 2010 and it gets all the chart in white. My conditional formatting of the numbers is greater than/ less than a value set by me. I have 18 rows, each formatted by a different value. My question is why the pie turns into white? I only have green and pink as colors. Thank you!

  66. Andrei –

    What are the values in the pie? Are the pie slices supposed to be one color or the other?

    If you step through the code, does it access each pie slice, does it determine each value properly, does it find the right range for the colors, does it find the right color for each value?

  67. Jon –
    I have, in my case, a column from D3 to D20 with cells which are colored by certain values of the numbesr inside them. For example, i formatted each column with greater than/less than a given number set by me.
    E.G. : in D3 if the number i put there is greater than 2800, the cell turns green; if not, it turns pink.
    in D4 if the number i put there is greater than 3500, the cell turns green; if not, it turns pink.
    I have no negative numbers or set values.
    I’m a begginer with Macro and that’s why i’m asking for your help.
    Thank you once again for understanding!

  68. Is it possible to do a similar color coding but instead of by value, by axis name. That is to say, from the original data in this thread, can you change the color of the bar based on the values of : Comcast, Brown & Williamson, Enron, Haliburton, regardless of their value?

  69. Kerry –

    That’s in a companion article: VBA Conditional Formatting of Charts by Category Label

  70. How do I conditionally format by value for a pie radar chart? Each wedge of the pie is equal size, but the wedge is filled according to value. The higher value, the more the wedge is filled. I’d like for the color to be conditional to the value (higher values green, lower red)

  71. I’m reluctant to get into this, because using color to encode more than qualitative information is problematic. Red and green, for instance, if you’re one of the over 8% of males with color vision deficiencies. In addition, displays with radial arrangements may lead to more confusion than the designer considered possible.

    What you need to do in this chart is have multiple data points for each segment, one for each color. Formulas in the source data show either a 1 for the color that should appear or 0 for colors that should not appear. The 1s appear as complete wedges, the zeros do not appear.

  72. Hi,
    Thanks for the great explanation and the code, it worked amazingly!
    I tried using the code for another project but unfortunately it didn’t work. It seems to be because the cells the chart is pulling the colours from are horizontal, rather than vertical. Therefore I just wanted to ask if there was a way to update and specify this in the code?
    Thanks,
    Asha

Trackbacks

  1. […] in but in my exercise the values that would dictate color would not be a part of the graph values. VBA Conditional Formatting of Charts by Value – Peltier Tech Blog Any suggestions would be welcome! Thank you for your time. […]

Speak Your Mind

*

Peltier Tech Charts for Excel 3.0