VBA - Adding patterns to bars in charts based on cell name

CSTjr

New Member
Joined
Jul 27, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hello first time here...I'll try to keep this short.
Im creating bar charts that use scores that are ranked and assign a color to each bar based on the name in the Color Legend. For some bars i also need to include a pattern to help differentiate similar names. i have been able to have the colors follow the bars without issue but i have been unable to have specific bars have a pattern. I cannot change names or go back and manually edit each time for other reasons. The image shows two bars, Mike and Mike2. i need Mike2 to also have a pattern, any pattern at this point included, that will follow the bar regardless of score/ position.

My "Chart"

Screen Shot 2022-07-27 at 1.56.44 PM.png


....and my VBA:

Sub ColorByCategoryLabel()
ActiveSheet.ChartObjects("Chart 1").Activate
Dim rPatterns As Range
Dim iCategory As Long
Dim vCategories As Variant
Dim rCategory As Range

Set rPatterns = ActiveSheet.Range("A5:A12")
With ActiveChart.SeriesCollection(1)
vCategories = .XValues
For iCategory = 1 To UBound(vCategories)
Set rCategory = rPatterns.Find(What:=vCategories(iCategory))
.Points(iCategory).Format.Fill.ForeColor.RGB = rCategory.Interior.Color
Next
End With
End Sub

Any help would be greatly appreciated.

Thanks!!!!

-CST
 

Attachments

  • 1658945159584.png
    1658945159584.png
    40.2 KB · Views: 11

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top