I thought you wanted to make the sheet visible if condition meet.

If you want to hide the sheet then just change 

Sheets("Sheet4").Visible = False
if you wany it automated add the code to the lookup sheet. e,g right click sheet name and select view code then add this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("A5") = "Yes" Then 'change to your condition
Sheets("Sheet4").Visible = False
End If
End Sub
VBA Noob