How to make a dynamic range in worksheet SelectionChange procedure in VBA event driven programming?

Question

I have to design a question paper template in excel, every question has four different types of answer like

"Yes/No", 
"Ture/False", 
"Multiple Choice/Single Answer",
"Multiple Choice/Multiple Answer".

if user select "Yes/No" and try to answer more then two answers then it pop up an error message. if user select "Multiple Choice/Multiple Answer" and select only one answer and move to another cell or column it popup error message. ...and so on for other types of questions.

I have use event-driven programming for this, use worksheet SelectionChange procedure. my code is working fine but it is not dynamic it is just working for only one question while I have 100 questions in my template.

four columns are used in this template Column A, B, C and D name respectively "Sr.No." Question no. is mention, "Question" Question is in this column, "Answer Type" Answer type is mention here in the drop-down list., "Correct Option" user need to be select True from here for the right option.

My code is here:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

' section 1

' for Multiple Choice/Multiple answer
' if user select less than 2 answer
rw = 9
rng1 = rw + 1
rng2 = rw + 4

If Worksheets("Question paper").Range("C" & rw).Value = "Mutilple Choice/ Mutiple Answer" And _
    WorksheetFunction.CountIf(Range("D" & rng1 & ":" & "D" & rng2), "TRUE") < 2 Then
    MsgBox "Select at least 2 ""TRUE"" option"

' for Multiple Choice/Single Answer
' if user select more than 1 answer
ElseIf Worksheets("Question paper").Range("C" & rw).Value = "Mutilple Choice/Single Answer" And _
    WorksheetFunction.CountIf(Range("D" & rng1 & ":" & "D" & rng2), "TRUE") > 1 Then
    MsgBox "Select only 1 ""TRUE"" option"

    ' if user doesn't select any answer
    ElseIf Worksheets("Question paper").Range("C" & rw).Value = "Mutilple Choice/Single Answer" And _
        WorksheetFunction.CountIf(Range("D" & rng1 & ":" & "D" & rng2), "TRUE") < 1 Then
        MsgBox "Select only 1 ""TRUE"" option"

' for Yes/No Answer
' if user select more than 1 answer
ElseIf Worksheets("Question paper").Range("C" & rw).Value = "Yes/No" And _
    WorksheetFunction.CountIf(Range("D" & rng1 & ":" & "D" & rng2), "TRUE") > 1 Then
    MsgBox "Select only 1 ""TRUE"" option"

        ' if user doesn't select any answer
        ElseIf Worksheets("Question paper").Range("C" & rw).Value = "Yes/No" And _
            WorksheetFunction.CountIf(Range("D" & rng1 & ":" & "D" & rng2), "TRUE") < 1 Then
            MsgBox "Select at least 1 ""TRUE"" option"

' for True/False Answer
' if user select more than 1 answer
ElseIf Worksheets("Question paper").Range("C" & rw).Value = "True/False" And _
    WorksheetFunction.CountIf(Range("D" & rng1 & ":" & "D" & rng2), "TRUE") > 1 Then
    MsgBox "Select only 1 ""TRUE"" option"

        ' if user doesn't select any answer
        ElseIf Worksheets("Question paper").Range("C" & rw).Value = "True/False" And _
            WorksheetFunction.CountIf(Range("D" & rng1 & ":" & "D" & rng2), "TRUE") < 1 Then
            MsgBox "Select at least 1 ""TRUE"" option"


' for Free Form(Essay) answer
ElseIf Worksheets("Question paper").Range("C" & rw).Value = "Free Form(Essay)" And _
    WorksheetFunction.CountIf(Range("D" & rng1 & ":" & "D" & rng2), "TRUE") > 0 Then
    MsgBox "Do not select any option,it is a free form question"

'ElseIf Worksheets("Question paper").Range("C" & rw).Value = "Free Form(Essay)" And _
'    Worksheets("Main Sheet").Range("C32") = "" Then
'    MsgBox "Please fill details in cell C32 in ""Main Sheet"" tab"

End If
End Sub

In my current code, It is just working for one question, to implement it for another 99 questions, I have to copy paste same code 99 times with assign a variable for question row number.

I expect a dynamic code for 100 or more question.

Answer 1

first I put this formula in another column =IF(AND(B10<>"",C10<>""),"Q",0) this formula give me the question number is which question user is working. In column "B" user need to enter a question and in column "C" there is a drop down for question type like "Yes/No", "Ture/False"... in column "D" I get the "Q" at the front of a question in which user is working. at the end of column "D", I put =sum formula so I got the question number.

in another 2 columns I make a table where I mention row number against each question like this: Q No. Row No. 1 10 2 15 3 25

now simply by using vlookup formula got the row number of question in which user is working.

now I simply assign this cell value in a variable in my VBA code like this: Private Sub Worksheet_SelectionChange(ByVal Target As Range)

rw = Range("XFB762").Value
rng1 = rw + 1
rng2 = rw + 4

If Worksheets("Question paper").Range("D" & rw).Value = "Multiple Choice/ Multiple Answer" And _
    WorksheetFunction.CountIf(Range("E" & rng1 & ":" & "E" & rng2), "TRUE") < 2 Then
    MsgBox "Select at least 2 ""TRUE"" option"

Now the range is dynamic . and it is working fantastic.

Answer 2

Tested it it works find if you use the Target.row value for your current row

rw = Target.row

because you only use a fixed value of 9 and not the current target row to check