Consulting

Results 1 to 10 of 10

Thread: Edit to "colour to chart" macro - to cycle through workbook

  1. #1

    Edit to "colour to chart" macro - to cycle through workbook

    Hi All,

    [Third time I have written this post - so my apologies if I miss anything!]

    I have been using a macro recently to colour up graphs based on client detail (c/o DataPig TechonologiesNQC Dashboard xID.xlsm) and it works really nicely on individual sheets. The macro is as follows:
    
    Sub CellColorsToChart()
    Dim oChart As ChartObject
    Dim MySeries As Series
    Dim FormulaSplit As Variant
    Dim SourceRange As Range
    Dim SourceRangeColor As Long
    Dim NumberofDataPoints As Long
    Dim iPoint As Long
    
    'Loop through all charts in the active sheet
    For Each oChart In ActiveSheet.ChartObjects
    
    'Loop through all series in the target chart
    For Each MySeries In oChart.Chart.SeriesCollection
    
    NumberofDataPoints = MySeries.Points.Count
    
    For iPoint = 1 To NumberofDataPoints
    
    'Get Source Data Range for the target series
    FormulaSplit = Split(MySeries.Formula, ",")
    
    'Capture the first cell in the source range then trap the color
    Set SourceRange = Range(FormulaSplit(2)).Item(iPoint)
    SourceRangeColor = SourceRange.DisplayFormat.Interior.Color
    
    On Error Resume Next
    'Coloring for Excel 2003
    'MySeries.Interior.Color = SourceRangeColor
    ' MySeries.MarkerBackgroundColorIndex = SourceRangeColor
    ' MySeries.MarkerForegroundColorIndex = SourceRangeColor
    
    'Coloring for Excel 2007 and 2010
    MySeries.Points(iPoint).MarkerBackgroundColor = SourceRangeColor
    MySeries.Points(iPoint).MarkerForegroundColor = SourceRangeColor
    MySeries.Points(iPoint).Format.Fill.ForeColor.RGB = SourceRangeColor
    
    Next
    Next MySeries
    Next oChart
    
    End Sub
    I now have a workbook consisting of two sheets: a data sheet (tabulated) and a dashboard show date and test selections and a plot based on these. What I would like is for the macro to be able to colour the plotted data points on sheet 2 based on the conditional formatting on sheet 1 (example attached). Can anyone help me with this please?
    Last edited by Paul_Hossler; 02-14-2018 at 08:51 AM. Reason: Added CODE tags

  2. #2
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    832
    Location
    Hi Moondrift and Welcome to this forum. You can trial this. HTH. Dave
    Sub CellColorsToChart()
    Dim oChart As ChartObject
    Dim MySeries As Series
    Dim FormulaSplit As Variant
    Dim SourceRange As Range
    Dim SourceRangeColor As Long
    Dim NumberofDataPoints As Long
    Dim iPoint As Long
    
    'Loop through all charts in the active sheet
    For Each oChart In Sheets("Sheet2").ChartObjects
     'Loop through all series in the target chart
     For Each MySeries In oChart.Chart.SeriesCollection
     NumberofDataPoints = MySeries.Points.Count
     For iPoint = 1 To NumberofDataPoints
     'Get Source Data Range for the target series
     FormulaSplit = Split(MySeries.Formula, ",")
     'Capture the first cell in the source range then trap the color
     Set SourceRange = Sheets("Sheet1").Range(FormulaSplit(2)).Item(iPoint)
     SourceRangeColor = SourceRange.DisplayFormat.Interior.Color
     On Error Resume Next
     'Coloring for Excel 2003
     'MySeries.Interior.Color = SourceRangeColor
     ' MySeries.MarkerBackgroundColorIndex = SourceRangeColor
     ' MySeries.MarkerForegroundColorIndex = SourceRangeColor
     'Coloring for Excel 2007 and 2010
     MySeries.Points(iPoint).MarkerBackgroundColor = SourceRangeColor
     MySeries.Points(iPoint).MarkerForegroundColor = SourceRangeColor
     MySeries.Points(iPoint).Format.Fill.ForeColor.RGB = SourceRangeColor
     Next
     Next MySeries
    Next oChart
    End Sub
    ps. please use code tags

  3. #3
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    At its simplest from a coding point of view you could try something like:
    Sub blah()
      For Each sht In Sheets
        sht.Activate
        CellColorsToChart
      Next sht
    End Sub
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  4. #4
    Hi Fellas,

    Sorry it has taken me so long to acknowledge your emails - I have been away from work, unwell.

    Unfortunately we haven't had a lot of luck with the getting the macro to work. It seems that the it does actually run, but the way that the pivot table selects the datapoints for plotting doesn't seem to lock the conditional formatting to the point being plotted (if that makes sense?!).

    We have managed a slightly cumbersome work around... By adding a third page to the workbook, so that we have "raw data", "selected data" and the "table/graph page". So I now have to run the macro to get the graph to update at all, but a least it is showing all the data with the correct colouration now.

    If you have any further suggestions, I'd love to hear from you.

    My apologies again taking so long to reply.

    Many thanks, MD.

  5. #5
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Can you supply a file with this sort of stuff in it? It will save us having to do it and prevent us guessing wrongly how your charts are set up.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  6. #6
    I thought I had added it last time, but I must have missed it somehow..?

    Cheers, M.
    Attached Files Attached Files

  7. #7
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Give me a couple of days (travelling).
    Others might jump in.

  8. #8
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Test thoroughly:
    Sub CellColorsToChart()
    Dim oChart As ChartObject
    Dim MySeries As Series
    Dim FormulaSplit As Variant
    Dim SourceRange As Range, cll As Range
    Dim SourceRangeColor As Long
    Dim i As Long
     
    'Loop through all charts in the active sheet
    For Each oChart In ActiveSheet.ChartObjects
      'Loop through all series in the target chart
      For Each MySeries In oChart.Chart.SeriesCollection
        'Get Source Data Range for the target series
        FormulaSplit = Split(MySeries.Formula, ",")
        Set SourceRange = Range(FormulaSplit(2)).SpecialCells(xlCellTypeVisible)
        i = 1
        'On Error Resume Next 'avoid if possible.
        For Each cll In SourceRange.Cells
          SourceRangeColor = cll.DisplayFormat.Interior.Color
          With MySeries.Points(i)
            .MarkerBackgroundColor = SourceRangeColor
            .MarkerForegroundColor = SourceRangeColor
            .Format.Fill.ForeColor.RGB = SourceRangeColor
          End With
          i = i + 1
        Next cll
        'On Error GoTo 0 'use only if you use On Error Resume Next above.
      Next MySeries
    Next oChart
    End Sub
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  9. #9
    Well, it's getting better. It is colouring the 'chunks' based on the colours selected, but it's almost like it is not recognising the dates in the associated cells?

    I may be asking way too much, so feel free to tell me it can't be done an move onto something more interesting

    Cheers, M.

  10. #10
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by Moondrift View Post
    but it's almost like it is not recognising the dates in the associated cells?
    Can you be more specific? What's not happenning that should be?
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

Posting Permissions

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