Forum Discussion

Copper Contributor

Aug 21, 2022

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 ...

excel

Formulas and Functions

Macros and VBA

Harun24HR

Aug 21, 2022

Rashaud35 You need nested IF() function. Try-

=IF(B4<=1200,IF(B5="Light",15000,IF(B5="HEAVY",30000,IF(B5="SUPER HEAVY",60000,""))),IF(B5="Light",25000,IF(B5="HEAVY",50000,IF(B5="SUPER HEAVY",75000,""))))

Subodh_Tiwari_sktneer

Silver Contributor

Aug 21, 2022

Rashaud35

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

Subodh_Tiwari_sktneer
Silver Contributor
Aug 21, 2022
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.