Add a fourth and fifth conditional formatting using vba

[Lplater]

I have a Master list of people in list A.
Additionally, I have five other separate lists all with named ranges from Team A through to Team E.

Have applied conditional formatting to list A "Master list" so that if any of the names from list A appear in either Team A, Team B or Team C, the names in my master list take on the colour I have deemed for each team.
Have done this using countif function on all cells in List A but unable to find a way to extend the cond formatting to my remaining 2 lists "Team D and Team E"

Can anyone please assist ??

Incidentally, the remaining 2 colours I need are pink and amber "pattern" with standard black font.

 

[kevatarvind]

which version of excel are you using if you are using 2007 or higher then you can add more then 3 condition in conditional formatting ?

 

[Lplater]

Unfortunately for the project I'm building, the operation is using 2003 and for the foreseeable future this won't be changing to 2007 or 2010 so I'm stuck with trying to get this feature working with 2003...

Have seen some private sub worksheet_change code using case etc but the ones I've seen utilise number values found in cells not "names of individuals' as is the case in my master list and in Teams A through E

 

[kevatarvind]

hi Check below link does this help you

More than 3 Conditional Formats in Microsoft Excel - How to? | Chandoo.org - Learn Microsoft Excel Online

 

[Lplater]

Yeah thanks, I did see that but it looks fairly complicated and it also looks like user input is required to achieve formatting each time the data changes.
I'm trying to build my sheet for the lowest common denominator operator so need it to be fully automatic without any input from operator.

Still trying to make sense of that myself actually....