lundi 28 août 2023

Using color fill and shaded pattern in a stacked column chart in VBA

would you mind to tell me how to use the following macro taken from this website ( see below ), if the data are in a stacked column, which means that I want the macro to apply to horizontal Axis Labels( Category ) not the Legend Entries (Series). I tried to change the macro to read the categories and not the series but it doesn’t work. I don’t know if I’m clear in my request. The code seems to work only for pie chart and simple histogram chart but not for stacked column chart. Also how to use color and shaded pattern not only filling the colors. Thank you very much for your help.

` Sub ColorByCategoryLabel()

Dim nChart As ChartObject Dim rPatterns As Range Dim iCategory As Long Dim vCategories As Variant Dim rCategory As Range Dim iSeries As Long

Set rPatterns = Worksheets("Colors").Range("Color")

For Each nCht In ActiveWorkbook.Worksheets("Pie Charts").ChartObjects For iSeries = 1 To 1

    With nCht.Chart.SeriesCollection(iSeries)
      
      vCategories = .XValues
      
      For iCategory = 1 To UBound(vCategories)
          Set rCategory = rPatterns.Find(What:=vCategories(iCategory), LookAt:=xlWhole)
      
          If rCategory Is Nothing Then
              MsgBox vCategories(iCategory) & " Not Found"
          Else
              .Points(iCategory).Format.Fill.ForeColor.RGB = rCategory.Interior.Color
              '.Points(iCategory).Format.Fill.ForeColor.RGB = rCategory.Interior.Pattern
          End If
      Next
        
    End With
  Next
Next

End Sub`

Aucun commentaire:

Enregistrer un commentaire