Excel 2013 Graph Conditional Formatting Using VBA

[ARIES_XIII]

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

 

[dave3009]

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]

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]

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]

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]

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

	A	B	C	D	E	F	G
Series 1	Series 2	Colour	Colour Scheme
Gas
Petrol
Oil
Natural Gas
Fracked Gas

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]49[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FF0000, align: right"]1[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFC000, align: right"]2[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]13[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #92D050, align: right"]3[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]45[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #00B0F0, align: right"]4[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]29[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #7030A0, align: right"]5[/TD]

</tbody>

Sheet1​

I then adapted Jon Peltiers' code to reflect some changes

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

 

[dave3009]

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]

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]

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?