I need help cell coloring based upon matching data on 2 sheets.

'Sheet1' - Data Dump
'Sheet2' - New Formatting

'Sheet1' column AD = "No" then find value on same row but in Column B
Then search on 'Sheet2' for that value in Column B. If found, color
the field in the row above it Red (Color.Index = 3)

I need to check each row of column AD on 'Sheet1' for value = "No" and
search all fields on 'Sheet2' for the value in Column B of same row.
The colored cell on 'Sheet2' will always in the the same column but 1
row up.

Thank you for any assistance you can provide.

Dwayne P.