If cell is this then change value to this (Help)

[Rashaud35]

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?

[#2 · Accepted solution]

@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,""))))

[#3]

@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

[#4]

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.

[#5]

@Harun24HR This worked!! Thank you so much for your help buddy. I could for the life of me get this figured out.

[#6]

Glad to know! If it helps then please tick mark the answer.