Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Mar 2014
    Posts
    14
    Thanks
    10
    Thanked 1 Time in 1 Post

    Automated Macro in Hourly Basis Pie

    Hi! I'm new in this macro. I'm trying to make a automated macro which will updated the pie based on the "Weather" & "Operation" column using conditional formatting, highlighting cell that contains/correspond to text.

    Unfortunately only outer pie works but using conditional formatting it disable or not allowing the macro to fill the said cell unless it will filled manually.

    To the masters of macro please help me to configure this excel.

    Here's what should happen to the pie.

    If I change the each row in Operation column to S or O the inner pie should also change the color based in the color shown in the operation
    and same as in the Weather Column if I change it to my desire weather condition the outer pie so should also change its color shown in the weather.

    Sorry for my bad English. Thanks in advance.

    Regards,

    Math
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If you set the RefersTo of the named range: Operation to
    ='24 Hours'!$C$3:$C$26

    You could use this modifiaction to your code:

    Code:
    Sub FillChartFromSeriesCells()
      Dim i As Integer
      Dim c As Range
      Dim pntPoint As Point
      Dim serSeries As Series
    
      With ActiveSheet
        Set serSeries = .ChartObjects(1).Chart.SeriesCollection(1)
        i = 1
        For Each c In Range("Weather")
          If c.Value <> "" Then
            Set pntPoint = serSeries.Points(i)
            pntPoint.Fill.ForeColor.SchemeColor = c.DisplayFormat.Interior.ColorIndex
            i = i + 1
          End If
        Next c
        Set serSeries = .ChartObjects(1).Chart.SeriesCollection(2)
        i = 1
        For Each c In Range("Operation")
          If c.Value <> "" Then
            Set pntPoint = serSeries.Points(i)
            pntPoint.Fill.ForeColor.SchemeColor = c.DisplayFormat.Interior.ColorIndex
            i = i + 1
          End If
        Next c
      End With
      Set pntPoint = Nothing
      Set serSeries = Nothing
    End Sub
    also if you add this to the worksheet object, it will do the updating automatically when a change is made in operation or weather range.
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
      If Not Intersect(Target, Union(Range("operation"), Range("weather"))) Is Nothing Then
        Call FillChartFromSeriesCells
      End If
    End Sub
    Steve

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    This code strays from what you did, but should be a little cleaner while accomplishing the same thing...

    Code:
    Sub FillChartFromSeriesCells()
      Dim i As Integer
      Dim x As Integer
      Dim cht As Chart
      Dim vArray As Variant
      
      vArray = Array("Weather", "Operation")
      Set cht = ActiveSheet.ChartObjects(1).Chart
      For i = 1 To 24
        For x = 0 To 1
          With Range(vArray(x)).Cells(i)
            If .Value <> "" Then
              cht.SeriesCollection(x + 1).Points(i).Fill.ForeColor.SchemeColor = _
                .DisplayFormat.Interior.ColorIndex
            End If
          End With
        Next
      Next
    End Sub
    Steve

  4. #4
    New Lounger
    Join Date
    Mar 2014
    Posts
    14
    Thanks
    10
    Thanked 1 Time in 1 Post
    Thanks a lot sir.

Posting Permissions

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