Consulting

Results 1 to 3 of 3

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
    549
    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 Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    3,933
    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 - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    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
  •