Need to automate based on set of values and populate
 

I just list out my real excel sheet below for your reference.

In an Excel sheet, First column represent the requirement for report needs.
Then remaining column are questions. Then business user (Business group) will
fill up the remaining columns (from G to O columns) with Values as YES or NO.
The YES or NO values are filled manually by the users.

I have created a sample code for one condition,

Sub TstMcro()

MsgBox Sheet1.Range("G2").Value & Sheet1.Range("H2").Value &
Sheet1.Range("J2").Value & Sheet1.Range("K2").Value & Sheet1.Range("L2").Value

If Sheet1.Range("G2").Value = "YES" And Sheet1.Range("H2").Value = "YES" And
Sheet1.Range("J2").Value = "YES" And Sheet1.Range("K2").Value = "YES" And
Sheet1.Range("L2").Value = "YES" Then

MsgBox "Four yes values"

Sheet1.Range("P2").Value = "ONE"

Else

MsgBox "all values are not yes values"

Sheet1.Range("P2").Value = Null

End If

End Sub



My question is how do i make this work for 30 rows of value for different
combination.

Thanks