+ Reply to Thread
Results 1 to 17 of 17

pie charts to match conditioning formatting colors of cell

Hybrid View

taffjohn pie charts to match... 09-19-2018, 08:21 AM
dflak Re: pie charts to match... 09-20-2018, 02:21 PM
taffjohn Re: pie charts to match... 09-21-2018, 09:05 AM
taffjohn Re: pie charts to match... 10-01-2018, 08:16 AM
romperstomper Re: pie charts to match... 10-01-2018, 08:51 AM
taffjohn Re: pie charts to match... 10-01-2018, 09:00 AM
romperstomper Re: pie charts to match... 10-01-2018, 09:05 AM
taffjohn Re: pie charts to match... 10-18-2018, 03:21 AM
romperstomper Re: pie charts to match... 10-18-2018, 03:34 AM
taffjohn Re: pie charts to match... 10-18-2018, 04:57 AM
romperstomper Re: pie charts to match... 10-18-2018, 05:55 AM
taffjohn Re: pie charts to match... 10-18-2018, 07:31 AM
romperstomper Re: pie charts to match... 10-18-2018, 07:57 AM
taffjohn Re: pie charts to match... 10-18-2018, 09:42 AM
taffjohn Re: pie charts to match... 10-18-2018, 08:57 AM
romperstomper Re: pie charts to match... 10-18-2018, 09:38 AM
romperstomper Re: pie charts to match... 10-18-2018, 09:48 AM
  1. #1
    Registered User
    Join Date
    09-20-2017
    Location
    Swansea
    MS-Off Ver
    2010
    Posts
    11

    pie charts to match conditioning formatting colors of cell

    Hi All

    This is my first post.

    I have created a Service sheet tab and once the work has been complete i mark it down and then in the Turbine Calc tab it conditional formatted the color, the problem i have is i need a macro to change the Doughnut chart in the Turbine Status tab to match the color of each cell in.

    I haven't done much with macros and ive tried so many online but non of them work, any help would be amazing.

    my file is 2mb so im unable to upload
    Attached Images Attached Images

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,957

    Re: pie charts to match conditioning formatting colors of cell

    Compress the file and post the ZIP file.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    09-20-2017
    Location
    Swansea
    MS-Off Ver
    2010
    Posts
    11

    Re: pie charts to match conditioning formatting colors of cell

    File zipped and attached

  4. #4
    Registered User
    Join Date
    09-20-2017
    Location
    Swansea
    MS-Off Ver
    2010
    Posts
    11

    Re: pie charts to match conditioning formatting colors of cell

    Any update if there is someone who could help me with this please?

  5. #5
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,971

    Re: pie charts to match conditioning formatting colors of cell

    Try this:

    Sub colourTheCharts()
        Dim co As ChartObject
        Dim sourceData As Range
        Dim n As Long
        Dim ser As Series
        Dim seriesFormula As String
        
        For Each co In ActiveSheet.ChartObjects
            Set ser = co.Chart.SeriesCollection(1)
            seriesFormula = Mid$(ser.Formula, 10)
            seriesFormula = Left$(seriesFormula, InStr(seriesFormula, ",") - 1)
            Set sourceData = Application.Range(seriesFormula)
            
            For n = 1 To ser.Points.Count
                With ser.Points(n).Format.Fill
                    .Visible = True
                    .Solid
                    .ForeColor.RGB = sourceData.Cells(n).DisplayFormat.Interior.Color
                End With
            Next n
            
        Next
    End Sub
    PS It doesn't really help to post a password protected project.
    Everyone who confuses correlation and causation ends up dead.

  6. #6
    Registered User
    Join Date
    09-20-2017
    Location
    Swansea
    MS-Off Ver
    2010
    Posts
    11

    Re: pie charts to match conditioning formatting colors of cell

    Thank you so much Rory that worked perfect.

    So sorry about the password i though i had removed it before uploading.

    I owe you

    Lee

  7. #7
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,971

    Re: pie charts to match conditioning formatting colors of cell

    No worries - doesn't take a lot to bypass.

  8. #8
    Registered User
    Join Date
    09-20-2017
    Location
    Swansea
    MS-Off Ver
    2010
    Posts
    11

    Re: pie charts to match conditioning formatting colors of cell

    Hi

    How do i make the macro run soon as ive made a change it updates the turbine status? Having to do it Manuel can be a pain.

  9. #9
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,971

    Re: pie charts to match conditioning formatting colors of cell

    You could put it in the worksheet_calculate event.

  10. #10
    Registered User
    Join Date
    09-20-2017
    Location
    Swansea
    MS-Off Ver
    2010
    Posts
    11

    Re: pie charts to match conditioning formatting colors of cell

    in the Macro? from above? is so where? sorry im not fully up to speed with macros

  11. #11
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,971

    Re: pie charts to match conditioning formatting colors of cell

    Right-click the worksheet tab, choose View code, then paste this in:

    private sub worksheet_calculate()
    colourTheCharts
    end sub

  12. #12
    Registered User
    Join Date
    09-20-2017
    Location
    Swansea
    MS-Off Ver
    2010
    Posts
    11

    Re: pie charts to match conditioning formatting colors of cell

    Hi Rorya

    So if ive done this right the code on the tab should look like the following but for some reason it still doesnt do it automatically.

    Private Sub worksheet_calculate()
    colourTheCharts
    End Sub

    Sub colourTheCharts()
    Dim co As ChartObject
    Dim sourceData As Range
    Dim n As Long
    Dim ser As Series
    Dim seriesFormula As String

    For Each co In ActiveSheet.ChartObjects
    Set ser = co.Chart.SeriesCollection(1)
    seriesFormula = Mid$(ser.Formula, 10)
    seriesFormula = Left$(seriesFormula, InStr(seriesFormula, ",") - 1)
    Set sourceData = Application.Range(seriesFormula)

    For n = 1 To ser.Points.Count
    With ser.Points(n).Format.Fill
    .Visible = True
    .Solid
    .ForeColor.RGB = sourceData.Cells(n).DisplayFormat.Interior.Color
    End With
    Next n

    Next
    End Sub

  13. #13
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,971

    Re: pie charts to match conditioning formatting colors of cell

    Can you post the workbook as you have it now?

  14. #14
    Registered User
    Join Date
    09-20-2017
    Location
    Swansea
    MS-Off Ver
    2010
    Posts
    11

    Re: pie charts to match conditioning formatting colors of cell

    Hopefully this works this time, i believe the password are off if not let me.
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    09-20-2017
    Location
    Swansea
    MS-Off Ver
    2010
    Posts
    11

    Re: pie charts to match conditioning formatting colors of cell

    Every time i try it to upload it even in zip it want upload

    Could you recommend anything?

  16. #16
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,971

    Re: pie charts to match conditioning formatting colors of cell

    I've just had a quick look at your earlier file - which sheet did you put the code into? It needs to be the charts' source data sheet, not the sheet with the charts on.

  17. #17
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,971

    Re: pie charts to match conditioning formatting colors of cell

    Put this code into the Turbine Calc sheet's module:

    Private Sub worksheet_calculate()
    colourTheCharts
    End Sub
    
    Sub colourTheCharts()
        Dim co As ChartObject
        Dim sourceData As Range
        Dim n As Long
        Dim ser As Series
        Dim seriesFormula As String
        
        For Each co In Sheets("Turbine Status").ChartObjects
            Set ser = co.Chart.SeriesCollection(1)
            seriesFormula = Mid$(ser.Formula, 10)
            seriesFormula = Left$(seriesFormula, InStr(seriesFormula, ",") - 1)
            Set sourceData = Application.Range(seriesFormula)
            
            For n = 1 To ser.Points.Count
                With ser.Points(n).Format.Fill
                    .Visible = True
                    .Solid
                    .ForeColor.RGB = sourceData.Cells(n).DisplayFormat.Interior.Color
                End With
            Next n
            
        Next
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. need help in conditioning formatting
    By AWIN in forum Excel General
    Replies: 8
    Last Post: 08-14-2018, 07:52 AM
  2. Need help applying Conditioning Formatting
    By kannon8833 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-09-2018, 10:25 AM
  3. [SOLVED] Vary Colors in Pivot Charts based on values (Overlay Charts)
    By TitansGo in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 01-18-2017, 04:36 PM
  4. [SOLVED] Excel 2007 : conditioning formatting a cell if another cell contain...
    By alex_shin in forum Excel General
    Replies: 5
    Last Post: 05-24-2012, 02:10 AM
  5. Conditioning formatting
    By Donald in forum Excel General
    Replies: 1
    Last Post: 11-29-2006, 07:30 PM
  6. Matching the colors Column Charts and Pie Charts
    By RohanSewgobind in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 04-21-2006, 04:40 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1