excel - VBA to update chart based on headers containing some text -

i have chart helps me plot out sequence of events person. need classes of events have consistency when chart.

for example, jane gets hired twice in career. want class of hire same. however, excel interrupt these different classes because 1 named 01-hire , other 02-hire. hires should blue in example below.

i want code search "hire" in header , then apply consistent color. note, there may different headers among sequences, code needs smart enough group things contain same text (not sequence number).

the closest thing find doing here: set color codes legends in vba

private sub formatshapelegend(sheet worksheet, legendname string, targetcolor msorgbtype)     dim shp shape     dim chrt chart     dim s series      each shp in sheet.shapes         if shp.haschart             set chrt = shp.chart              'loop dataseries find legend desired name.             each s in chrt.seriescollection                 'if name fits, go ahead , format series.                 if lcase(s.name) = lcase(legendname)                     s.format.fill.forecolor.rgb = targetcolor                 end if             next         end if     next end sub  formatshapelegend activesheet, "iso", rgb(0, 0, 255) 

i want classes below similar chart.

desired output

the data table

the raw code row labels 01 - hire 01 - promotion 01 - term 02 - hire 02 - promotion 02 - term 03 - hire 03 - promotion 03 - term jane 38 10 29
ben 15 50 10 joe 68 56 10 7
lisa 61 41
jenny 24
jerry 81 16

if series labels repeating "hire x","prom x","term x" work:

dim s series, x long x = 0  each s in activesheet.chartobjects(1).chart.seriescollection     x = x + 1     s.format.fill.forecolor.rgb = array(vbblue, vbred, vbgreen)(x mod 3) next s 

if need based off series name then:

dim s series, clr long, nm string  each s in activesheet.chartobjects(1).chart.seriescollection      nm = lcase(s.name)      clr = vbyellow 'default     if nm "*hire*"         clr = vbblue     elseif nm "*prom*"         clr = vbgreen     elseif nm "*term*"         clr = vbred     end if      s.format.fill.forecolor.rgb = clr  next s