Excel 2013 Graph Conditional Formatting Using VBA

ARIES_XIII

New Member
Joined
Mar 28, 2012
Messages
14
Hello Excel Folks,

I would like to create a script that would color points on a scatter point by a value (1 through 8). The data setup is straight forward ... a X values column, a Y values column, and a Color column (which contains the values for each color).

The Peltier Tech created something close to what I am interested 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.

ARIES_XIII :)
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
365, 2016
Hi ARIES_XIII,

I think you might need to expand a little, you tell us that you have a chart and that the formatting conditions are not based on the values in that chart. You didn't tell us what the format conditions would be based on, you might find people will be more likely to engage if they knew rather than taking a guess and spending time on a solution that could never fit the needs of your exercise. ;)

Regards

Dave
 

ARIES_XIII

New Member
Joined
Mar 28, 2012
Messages
14
I apologize, I am not sure what is missing? You have a X/Y columns provide a coordinate, excel reads/plots the point, and numerical value in third column which would be associated with a color (1 for green, 2 orange, etc). The script I imagine could read the third column and color the point accordingly. If you want context, it is a graph for Gas-Filled Porosity versus Permeability. The color is supposed to indicate the petrophysical rock facies.
 

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
365, 2016
I see, I thought the colour column meant something different seeing you couldn't use the peltiertech.com example.

So using the example and given you have two sets of data you'd need to pass over SeriesCollections 1 & 2, perhaps in a loop or just code one after the other, vPatterns would be set to your pattern range and vValues would be that colour column.
 

ARIES_XIII

New Member
Joined
Mar 28, 2012
Messages
14
Sorry, I am a complete and utter novice when it comes to VBA. Picking a source to learn the language is difficult. Thus I am here asking the world of excel users. Could you show me what that would look like? Inverclyde, cool!
 

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
365, 2016
OK

I'll do my best to make the alterations and show how it might work. This is an example so you will have to adapt it to your needs.

First I set up a best guess type data set

Excel 2007
ABCDEFG
1Series 1Series 2ColourColour Scheme
2Gas494611
3Petrol101022
4Oil131813
5Natural Gas451814
6Fracked Gas293325

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1



I then adapted Jon Peltiers' code to reflect some changes

Rich (BB code):
Sub ColorByValue()
  Dim rPatterns As Range
  Dim iPattern As Long
  Dim vPatterns As Variant
  Dim iPoint As Long
  Dim sLoop As Integer ' Hold a variable for looping series collections
  Dim vValues As Variant
  Dim rValues As Range
  Dim rValue As Range


  ' ****Uncomment the line below if the chart will not be active when the code is run ****
  ' ChartObjects("Chart 1").Activate ' Change "Chart 1" to correct chart name



  Set rPatterns = ActiveSheet.Range("G2:G6") ' Hold the colors and values for conditional change
  Set rValues = ActiveSheet.Range("D2:D6") ' color column
  vPatterns = rPatterns.Value
  vValues = rValues.Value
    
    For sLoop = 1 To 2 ' 1 to [No. of Chart Series] in ARIES_XIII case it is 2
        With ActiveChart.SeriesCollection(sLoop)
            For iPoint = 1 To UBound(vValues)
                For iPattern = 1 To UBound(vPatterns)
                    If vValues(iPoint, 1) <= vPatterns(iPattern, 1) Then
                        .Points(iPoint).Format.Fill.ForeColor.RGB = _
                            rPatterns.Cells(iPattern, 1).Interior.Color
                        Exit For
                    End If
                Next iPattern
            Next iPoint
        End With
    Next sLoop


End Sub

From which I get a basic chart that looks like this



Results will clearly vary depending on number of points and colours, and in order for that code to run correctly the chart will need to be active, I have included a line that can be uncommented to make this happen.

EDIT: OK, I made one tiny change before making the chart image and that was to change colour column third colour number to 5, I still had the original set on my clipboard. Hence the middle series is purple :)
 
Last edited:

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
365, 2016
I will also mention that it is possible to achieve the same results without VBA, it's a little more involved to set up but it would mean not having to run the macro each time you updated the colour column, which you might prefer if you aren't massively confident with VBA.
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,847
Office Version
365
Platform
Windows, MacOS
Your best bet is to use the non-VBA approach in my tutorial Conditional Formatting of Excel Charts. My example has the five columns C through G for applying five formats to the data. You will need eight columns, one for each of the formats. Depending on the value in the "Format" column (1 through 8) these eight columns will either show the value if the color number matches, or #N/A if it doesn't.
 

ARIES_XIII

New Member
Joined
Mar 28, 2012
Messages
14
Thanks Dave! That is perfect! The Peltier Tech ... cool. I used the tiered approach before. I have a very large data set and wanted something a little more automated. Do y'all have any recommendations for source to learn VBA?
 

Watch MrExcel Video

Forum statistics

Threads
1,095,158
Messages
5,442,731
Members
405,194
Latest member
Aprivate

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top