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

[9davros8]

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:

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?)

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