If I get it, and there is only 1 cell address to be changed (B2) and not too many possibilities (for the contents of B2) to examine, then use a Select Case block? If I remember this event, it fires when any cell is changed on the sheet, so just examine the contents of B2.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B2")) Is Nothing Then
Select Case Range("B2")
Case "Regular"
Range("F2:J2").ClearContents
Case "Regular (extra)"
Range("G2:J2").ClearContents
'...and so on
End Select
End If
End Sub
If you want to vary the range based on some input, declare a range variable and assign the address to it, then use the variable in the Select statements.
Not sure if you need to do anything further to examine the contents of B2 as in Select Case Range("B2").something or copy and assign to a variable and use the variable in the Select statement. My Excel vba knowledge is not real deep.
Last edited by Micron; 08-20-2017 at 07:53 PM.
Reason: added info
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.