Conditional formatting/lookup/IF

Hi....first time poster. Hope im in the right section

I have a a worksheet shown below called ADE. There are a number of columns as you can see.

Example 1.jpg

I also have a worksheet called Game by Game shown below

Example 2.jpg

I want to do some conditional formatting - if Vance's name appears in cell B4 of worksheet game by game then I want the number 3 to appear automatically in cell B2 of worksheet ADE.

However, if Vance isn't in B4 of of game by game I want it to look at Cell B5 - if it is there then I want the number 2 to appear in B2 of ADE

Again, if Vance doesn't in Cell B5 then I want it to look in B6, and if Vance is there then I want the number 1 to appear in B2 of ADE.

If Vance is in none of those cells, I then want it to look in cell B8, and if B8 contains Vance then I want B2 of ADE to be shaded green.

Finally, if if Vance is in neither B4, B5, B6 or B8 then cell B2 in ADE can be left blank

I hope this makes sense - I have tried using LOOKUP and IF functions to do this but I am getting tangled up. I only need it for one example and then I can manipulate it for anything else