Don't know exactly what you want but this would seem to do it
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Range("D44") = "YES" And Range("D45") = "YES" Then
Rows("46:47").EntireRow.Hidden = False
Rows("46:47").EntireRow.Hidden = True
Application.ScreenUpdating = True
The 'Target' argument referes to the worksheet's active cell (that has been changed) not the worksheet itself.