VBA Code Help - Relating to Colour Fill of Pie Charts

[Steaky]

Hi all,

Looking for some guidance. I'm wondering if anyone knows of any way to edit this VBA code so that it works with cells that are coloured via conditional formatting?

If not, is it possible to bypass this issue (currently, my poor pie chart is now blank!) by using a VBA code to colour in the cells that my pie chart refers to?

 Sub ColorPies()
    Dim cht As ChartObject
    Dim i As Integer
    Dim vntValues As Variant
    Dim s As String
    Dim myseries As Series
     
        For Each cht In ActiveSheet.ChartObjects
            For Each myseries In cht.Chart.SeriesCollection
     
                If myseries.ChartType <> xlPie Then GoTo SkipNotPie
                s = Split(myseries.Formula, ",")(2)
                vntValues = myseries.Values
               
                For i = 1 To UBound(vntValues)
                    myseries.Points(i).Interior.Color = Range(s).Cells(i).Interior.Color
                Next i
SkipNotPie:
            Next myseries
        Next cht
    End Sub

Thank you!

 

[AlphaFrog]

Instead of trying to read the Conditional Formatting (CF) color, you could code your macro to test for the same "conditions" used in the CF formulas.

Replace this line...
myseries.Points(i).Interior.Color = Range(s).Cells(i).Interior.Color
...with the same CF formulas whatever they may be.

 

[Steaky]

I really don't know much at all about macro codes.


If my conditional formatting in a cell is to colour the cell based on the contents of another cell, how would this look in code?


I appreciate any help

 

[AlphaFrog]

I really don't know much at all about macro codes.


If my conditional formatting in a cell is to colour the cell based on the contents of another cell, how would this look in code?


I appreciate any help

Can you show your CF formua(s) and what color they have?

 

[Steaky]

I'm at work at the minute, it would maybe be best if I attach my worksheet when I get home.


My conditional formatting is along the lines of (from memory):


In cell B9:B13: if E2:E6 = 1, colour green


If E2:E6 = 0, colour red.


B9 is looking up E2, B10 looks up E3 and so on.


Thank you for helping me with this!

 

[Steaky]

https://docs.google.com/file/d/0B6EN1YGXV1_qUDRiLURZRWxvMEU/edit?usp=sharing


I think that should work as a link.

Cell B9 has an example of the conditonal formatting I'm looking for. Though, actually need to add in another conditional format for amber.