Excel 2013 Graph Conditional Formatting Using VBA

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Excel 2013 Graph Conditional Formatting Using VBA

  1. #1
    New Member
    Join Date
    Mar 2012
    Location
    Houston, Texas
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Excel 2013 Graph Conditional Formatting Using VBA

     
    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

  2. #2
    Board Regular dave3009's Avatar
    Join Date
    Jun 2006
    Location
    Inverclyde, Scotland
    Posts
    6,996
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel 2013 Graph Conditional Formatting Using VBA

    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
    Please state your version of Excel, I use Excel 2007 on Win 10.
    Back up all data and test VBA code on a COPY workbook, and please use [code][/code] tags.
    HTML Maker | HTML Maker - FAQ

  3. #3
    New Member
    Join Date
    Mar 2012
    Location
    Houston, Texas
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel 2013 Graph Conditional Formatting Using VBA

    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.

  4. #4
    Board Regular dave3009's Avatar
    Join Date
    Jun 2006
    Location
    Inverclyde, Scotland
    Posts
    6,996
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel 2013 Graph Conditional Formatting Using VBA

    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.
    Please state your version of Excel, I use Excel 2007 on Win 10.
    Back up all data and test VBA code on a COPY workbook, and please use [code][/code] tags.
    HTML Maker | HTML Maker - FAQ

  5. #5
    New Member
    Join Date
    Mar 2012
    Location
    Houston, Texas
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel 2013 Graph Conditional Formatting Using VBA

    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!

  6. #6
    Board Regular dave3009's Avatar
    Join Date
    Jun 2006
    Location
    Inverclyde, Scotland
    Posts
    6,996
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel 2013 Graph Conditional Formatting Using VBA

    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
    1 Series 1 Series 2 Colour Colour Scheme
    2 Gas 49 46 1 1
    3 Petrol 10 10 2 2
    4 Oil 13 18 1 3
    5 Natural Gas 45 18 1 4
    6 Fracked Gas 29 33 2 5
    Sheet1



    I then adapted Jon Peltiers' code to reflect some changes

    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 by dave3009; Apr 14th, 2016 at 02:01 AM.
    Please state your version of Excel, I use Excel 2007 on Win 10.
    Back up all data and test VBA code on a COPY workbook, and please use [code][/code] tags.
    HTML Maker | HTML Maker - FAQ

  7. #7
    Board Regular dave3009's Avatar
    Join Date
    Jun 2006
    Location
    Inverclyde, Scotland
    Posts
    6,996
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel 2013 Graph Conditional Formatting Using VBA

    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.
    Please state your version of Excel, I use Excel 2007 on Win 10.
    Back up all data and test VBA code on a COPY workbook, and please use [code][/code] tags.
    HTML Maker | HTML Maker - FAQ

  8. #8
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    4,400
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel 2013 Graph Conditional Formatting Using VBA

    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.
    Jon Peltier
    Peltier Technical Services, Inc.
    Try Peltier Tech Charts for Excel

  9. #9
    New Member
    Join Date
    Mar 2012
    Location
    Houston, Texas
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel 2013 Graph Conditional Formatting Using VBA

      
    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?

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com