Hi all!
Someone by the name of Andrew very kindly passed on some VBA code for highlighting cells with colours and then having a macro put the corresponding colours within bars on a chart. The following coding worked great, but then I put some conditional formatting on the cells so that when I updated my data (sourced from SQL) the dates within the data for weekdays (Mon to Fri) and weekends (Sat & Sun) stayed the same - the macro then stopped working. When I update the data on a daily basis I have two weeks worth of rows and lose a day at the top of the dataset and gain a day at the bottom - without a conditional format my cell colours for weekdays/weekends goes out of alignment. I don't want to keep changing the colours on the data when a lose/gain a day etc. Is there any way around the conditional formatting? If there's anyone who could help me out with this I'd really appreciate it - thank you! Coding Andrew supplied is as follows...
Sub CellColorsToChart() Dim oChart As ChartObject Dim MySeries As Series Dim i As Long Dim FormulaSplit As Variant Dim SourceRange As Range Dim Cell As Range Dim SourceRangeColor 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 ' Reset Points counter i = 0 ' Get Source Data Range for the target series FormulaSplit = Split(MySeries.Formula, ",") ' Capture the source range Set SourceRange = Range(FormulaSplit(2)) ' Loop around the cells in Sourcerange For Each Cell In SourceRange ' Get the cell's color SourceRangeColor = Cell.Interior.Color ' Increment Points counter i = i + 1 With MySeries.Points(i) On Error Resume Next ' Coloring for Excel 2003 .Interior.Color = SourceRangeColor .Border.Color = SourceRangeColor .MarkerBackgroundColorIndex = SourceRangeColor .MarkerForegroundColorIndex = SourceRangeColor ' Coloring for Excel 2007 and 2010 .MarkerBackgroundColor = SourceRangeColor .MarkerForegroundColor = SourceRangeColor .Format.Line.ForeColor.RGB = SourceRangeColor .Format.Line.BackColor.RGB = SourceRangeColor .Format.Fill.ForeColor.RGB = SourceRangeColor On Error GoTo 0 End With Next Cell Next MySeries Next oChart End Sub</pre>
Someone by the name of Andrew very kindly passed on some VBA code for highlighting cells with colours and then having a macro put the corresponding colours within bars on a chart. The following coding worked great, but then I put some conditional formatting on the cells so that when I updated my data (sourced from SQL) the dates within the data for weekdays (Mon to Fri) and weekends (Sat & Sun) stayed the same - the macro then stopped working. When I update the data on a daily basis I have two weeks worth of rows and lose a day at the top of the dataset and gain a day at the bottom - without a conditional format my cell colours for weekdays/weekends goes out of alignment. I don't want to keep changing the colours on the data when a lose/gain a day etc. Is there any way around the conditional formatting? If there's anyone who could help me out with this I'd really appreciate it - thank you! Coding Andrew supplied is as follows...
Sub CellColorsToChart() Dim oChart As ChartObject Dim MySeries As Series Dim i As Long Dim FormulaSplit As Variant Dim SourceRange As Range Dim Cell As Range Dim SourceRangeColor 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 ' Reset Points counter i = 0 ' Get Source Data Range for the target series FormulaSplit = Split(MySeries.Formula, ",") ' Capture the source range Set SourceRange = Range(FormulaSplit(2)) ' Loop around the cells in Sourcerange For Each Cell In SourceRange ' Get the cell's color SourceRangeColor = Cell.Interior.Color ' Increment Points counter i = i + 1 With MySeries.Points(i) On Error Resume Next ' Coloring for Excel 2003 .Interior.Color = SourceRangeColor .Border.Color = SourceRangeColor .MarkerBackgroundColorIndex = SourceRangeColor .MarkerForegroundColorIndex = SourceRangeColor ' Coloring for Excel 2007 and 2010 .MarkerBackgroundColor = SourceRangeColor .MarkerForegroundColor = SourceRangeColor .Format.Line.ForeColor.RGB = SourceRangeColor .Format.Line.BackColor.RGB = SourceRangeColor .Format.Fill.ForeColor.RGB = SourceRangeColor On Error GoTo 0 End With Next Cell Next MySeries Next oChart End Sub</pre>
