Forum Discussion
Solved
If cell is this then change value to this (Help)
Hello, I was hoping for a little assistance if possible:
When a value is manually entered into cell B4 and that value is less than or equal to 1200, then cell A27 should read out values assigned to different options associated with a dropdown list designated in cell B5.
Cell B5 dropdown list is as follows and are defined on Sheet 2 (cells A2 to A4):
Light
Heavy
Super Heavy
If the value manually entered into cell B4 is less than or equal to 1200, then I would like cell A27 to produce the following values:
IF (B5="Light",15000, IF (B5="HEAVY",30000, IF (B5="SUPER HEAVY",60000))))
But.
If the value entered into cell B4 is greater than or equal to 1201, then I would like cell A27 to produce the follow DIFFERENT values:
IF (B5="Light",25000, IF (B5="HEAVY",50000, IF (B5="SUPER HEAVY",75000))))
Any idea how I can achieve this?
You may try something like this...
Private Sub Worksheet_Change(ByVal Target As Range) If Target.CountLarge > 1 Then Exit Sub On Error GoTo Skip If Target.Address(0, 0) = "B4" Then Application.EnableEvents = False If IsNumeric(Target.Value) Then If Target <= 1200 Then Range("A27").Formula = "=IF(B5=""Light"",15000,IF(B5=""HEAVY"",30000,IF(B5=""SUPER HEAVY"",60000)))" Else Range("A27").Formula = "=IF(B5=""Light"",25000,IF(B5=""HEAVY"",50000,IF(B5=""SUPER HEAVY"",75000)))" End If Else Range("A27").Value = "" End If End If Skip: Application.EnableEvents = True End Sub- Place the above code on Sheet Module and to do so, right-click on Sheet Tab Name and choose "View Code", paste the above code into the opened code window and save your file as Macro-Enabled Workbook.
