change colour of multiple bar charts columns based on cell background colour

9davros8

New Member
Joined
Jul 11, 2017
Messages
21
hi guys
been a while since I posted so bear with me and I hope one of you can help. what i'm trying to achieve is to create a bar chart where each individual column's colour is based on the colour of the cell background colour. however this is for multiple charts.

nice and simply I have a sheet with multiple lines and on each line is a property name and in the same row multiple cells of numerical data. so for example ( I can't post the data for security reasons but you get the gist):
so on sheet 1 I have

property A 23 43 43 45 31
property B 20 49 31 21 09

I have about 200 properties. my first goal was to get some VBA code which would create charts on sheet 2 for all individual rows - so about 200 bar charts. you lot helped me and the code below works to do this:

VBA Code:
Sub WW_chart_3()
' from sheet1 this gets the data and creates charts for all the propereties in sheet2
'variable declaration
Dim i As Long
Dim LastRow As Long
Dim LastColumn As Long
    Dim chrt As Chart

    'Find the last used row
LastRow = Sheets("Sheet1").Range("A65536").End(xlUp).Row
'LastRow = Cells(Rows.Count, 1).End(xlUp).Row



'Find the last used column
    LastColumn = Sheets("Sheet1").Range("A1").End(xlToRight).Column

    'Looping from second row till last row which has the data
For i = 2 To LastRow
'Sheet 2 is selected bcoz charts will be inserted here
        Sheets("Sheet2").Select

        'Adds chart to the sheet
Set chrt = Sheets("Sheet2").Shapes.AddChart.Chart
'sets the chart type
chrt.ChartType = xlColumnClustered
'now the line chart is added...setting its data source here
With Sheets("Sheet1")


chrt.SetSourceData Source:=Union(.Range(.Cells(1, 1), .Cells(1, LastColumn)), .Range(.Cells(i, 1), .Cells(i, LastColumn)))


        End With

        'Left & top are used to adjust the position of chart on sheet
' CODE BELOW IS WORKING
'chrt.ChartArea.Left = 1
        'chrt.ChartArea.Top = (i - 2) * chrt.ChartArea.Height

    ' TEST CODE FOR CHART SIZE AND POSITION
chrt.ChartArea.Left = 1

'add trendline

' RESIZE CHART
chrt.ChartArea.Width = 800
chrt.ChartArea.Height = 250
    chrt.ChartArea.Top = (i - 2) * chrt.ChartArea.Height

        Next      

End Sub

so thanks for this. however, taking each individual row separately in sheet 1 , the numerical data is either estimated or actual and usually a mix of both on each row. so I want to use the cell background colour red for estimated data and blue for actual data (or whatever colours I choose). I need the charts created by the VBA code above to reflect the cell background colour for each individual cell.

I have found this code below but have no idea how to incorporate it into the code above (thanks to whoever created this on How to color chart based on cell color in Excel?)

VBA Code:
Sub CellColorsToChart()
'Updateby Extendoffice
    Dim xChart As Chart
    Dim I As Long, J As Long
    Dim xRowsOrCols As Long, xSCount As Long
    Dim xRg As Range, xCell As Range
    On Error Resume Next
    Set xChart = ActiveSheet.ChartObjects("Chart 1").Chart
    If xChart Is Nothing Then Exit Sub
    xSCount = xChart.SeriesCollection.Count
    For I = 1 To xSCount
        J = 1
        With xChart.SeriesCollection(I)
            Set xRg = ActiveSheet.Range(Split(Split(.Formula, ",")(2), "!")(1))
            If xSCount > 4 Then
                xRowsOrCols = xRg.Columns.Count
            Else
                xRowsOrCols = xRg.Rows.Count
            End If
            For Each xCell In xRg
                .Points(J).Format.Fill.ForeColor.RGB = ThisWorkbook.Colors(xCell.Interior.ColorIndex)
                .Points(J).Format.Line.ForeColor.RGB = ThisWorkbook.Colors(xCell.Interior.ColorIndex)
                J = J + 1
            Next
        End With
    Next
End Sub

any help incorporating the above code (if it is what i'm looking for and will do the job??) would be appreciated.

why so many charts? - i'm working on energy consumption and it's easier to look at graphs with a trendline to easily see what properties are using above average energy then is it just to look at numerical data

thanks for help in advance
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Watch MrExcel Video

Forum statistics

Threads
1,127,552
Messages
5,625,464
Members
416,108
Latest member
Ravenhold

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top