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 :)
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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

2eltc7o.png


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:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top