conditional formating over two ranges

[0lzi]

hi,

essentialy, i want one cell to change colour to green when cells on another table in two different colums contain the word yes.

Sheet("Project 185") cells are , C14 and C16 AND E7:E12

if all of these cells contain the word "Yes" then make cell on sheet Master Sheet D3

if some are yes then Cell on Master Sheet orange
if none are yes then cell on master sheet red

i tried with a formula but failed

=IF('Project 185 '!E7:E12) AND ('Project 185 '!C14:C16 = "Yes", "Yes","No")

also tried in vba

Private Sub Sheet_Open()
If Sheets("Project 185").Range("E7:E12") And Sheets("Project 185").Range("C14:C16") = "Yes" Then
    Sheets("Master Sheet").Range("D3") = "Yes"
    
End If
End Sub

these two attempts were just to put a yes or no in place then use the conditional formating tool to change colour that way.

thanks in advance

 

[0lzi]

i have also tried to move the data out of the wierd cell ranges to a table to vlookup

=IF(ISNA(VLOOKUP("Pending",Sheet1!A1:B7,1,FALSE))="Pending","P",IF(ISNA(VLOOKUP("No",Sheet1!A1:B7,1,FALSE))="No","N","Y"))

the logic im using is:

If there is a NO put a N
else if there is Pending put Pending
Else put Yes

the cell doenst change from Y even if there is a No or Pending in the table.