Help with VBA

[terry_m]

I have a worksheet that has multiple dropdowns. There is one column (B6:B35) that has several values in the dropdown but if a specific value is selected in one cell I need to force 3 other cells in the same row to be set to a specific value.
Example
If B6 is changed to "Cloud Provider", I need C6 to automatically change to "Subscription", H6 to automatically change to "Consumption" and I6 to automatically change to "SaaS Consumption".
Or if B7 is changed to "Cloud Provider", I need C7, H7, and I7 to automatically change the same way.
Any cell in the range (B6:B35) needs to follow this rule.

As it stands I was able to get it to work if I wrote it like this....
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$B$6" Then
If Target.Value = "Cloud Provider" Then
Range("C6") = "Subscription"
Range("H6") = "Consumption"
Range("I6") = "SaaS Consumption"
MsgBox "B:6 was set to Cloud Provider. That value set the Overall type of the software in C:6, Metric Group in H:6, and the License Metric in I:6."
End If
End If

If Target.Address = "$B$7" Then
If Target.Value = "Cloud Provider" Then
Range("C7") = "Subscription"
Range("H7") = "Consumption"
Range("I7") = "SaaS Consumption"
MsgBox "B:7 was set to Cloud Provider. That value set the Overall type of the software in C:7, Metric Group in H:7, and the License Metric in I:7."
End If
End If

If Target.Address = "$B$8" Then......

End Sub

But that means I need to do that for each row in the spreadsheet. I'm sure there is a simpler why for this to be done but I'm at a lost. Can anyone help?

 

[MARK858]

Try something like...

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("B6:B35")) Is Nothing Then
        Application.EnableEvents = False
        If Target.Value = "Cloud Provider" Then
            Target.Offset(, 1) = "Subscription"
            Target.Offset(, 6) = "Consumption"
            Target.Offset(, 7) = "SaaS Consumption"
            MsgBox "B:" & Target.Row & " was set to Cloud Provider. That value set the Overall type of the software in C:" & Target.Row & ", Metric Group in H:" & Target.Row & ", and the License Metric in I:" & Target.Row & "."
        End If
        Application.EnableEvents = True
    End If

End Sub

Welcome to the forum but can you please use code tags in future

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

 

[terry_m]

Thank you so much, that is perfect!

 

[MARK858]

You're welcome, happy it helped