|You can do that with Conditional Formatting.|
My example will only use 2 sheets (not 4) so simply expand the concept to get it to work for any number of sheets.
First, since Conditional Formatting cannot reference other sheets in it's formulas, we need to use named ranges.
Let's say my Roster is in Sheet1!A1:A5.
Lets' say the names I am searching for are in Sheet2!A1:A5 and Sheet3!A1:A5.
1 - Select Sheet2!A1:A5 and name the range. I used "mySheet2"
2 - Select Sheet3!A1:A5 and name the range. I used "mySheet3"
3 - Select Sheet1!A1:A5 and click on Format...Conditional Formatting...Formula is
4 - Paste this into the formula Field and choose a Red fill format:
5 - Click Add, Formula Is, and paste this into the new formula Field, choose a Yellow fill format:
What this will do is count how many times each name appears in the named Ranges. If the sum of the counts is greater than 1, the cell will turn Red. If it equals 1, it'll turn yellow.
Hope that helps!
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.