Conditional formatting by matching text from a list

Hi all - another new one from me.

I have a spreadsheet used for resourcing people on projects. It is currently set up with very basic conditional formatting to colour in a cell when I type in a person's initals - eg. typing 'JG' will turn the cell green.

I currently have a separate conditional formatting rule for each individual (there are a lot). This makes the spreadsheet a little cumbersome, especially when the rules decide to reset themselves from covering entire columns as was the intention, to duplicating themselves to odd specific ranges (pesky colleagues copying and pasting) and losing my nice 'E:E' ones. This also means the rules need updating every time there is a personnel change.

The neat solution I would like to implement is to simply have a list of the personnel initals on another tab of the worksheet, and use a formula in the conditional formatting to identify that the initials I have typed into the cell come from the specified range and thus the cell should be green.

So if the list in column A of this second tab is:
JG
KH
LI

I would like to be able to type any of these sets of initials into my cell on the first tab and any one of them would turn the cell green.

Can anyone help with this formula?

I would also have other ranges that would turn the cells other colours if the relevant text is typed in, so the formula would need to allow for that.

Cheers,
James

it may help if you upload an example file

Example spreadsheet attached!
J

Hello James
Take a look at the attached reply (CF Example) tab. I've created 6 named ranges (colours) and used the Match function for those which have an exact match and the Countif function with a Wildcard (*) for the cells with variable initials before the dash (/R/D). These functions are all placed within the Conditionl Formatting for the range F8:O24. There are now 6 conditons applied to the range.

Hope this is what you're looking for.
DBY

So simple - named ranges! Learn something new every day...

Thanks very much - worked like a charm!

Cheers,
James