2 conditions in Worksheet_Change sub

[BMD44]

Hello,

I have a requirement where in a message needs to be displayed based on the cell value in the sheet which works as expected which is below.

Now, I need another check and message to be displayed based on a different cell value when the value changes which is the second section of the code that doesn't work. Can anyone please suggest how can we have multiple checks in Worksheet_Change Sub.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$2" Then
        If Target.Value = "Yes" Then
            MsgBox "Check required."
        ElseIf Target.Value = "No" Then
            MsgBox "Check not required"
        End If
    End If

  If Target.Address = "$E$2" Then
        If Target.Value = "Yes" Then
            MsgBox "Total > 500"
        ElseIf Target.Value = "No" Then
            MsgBox "Total < 500"
        End If
    End If
   
End Sub

 

[Micron]

Doesn't work means what?
The code looks fine - as long as you want something to happen for Yes or No but only for ranges D2 and E2.
However, you're checking for No and Yes. If the cell contains NO or no and YES or yes then that's probably your issue.

 

[BMD44]

Hello,

I have "Yes" and "No". While for the first scenario, I see a message when the drop down selection is Yes/No. For the second scenario even if the value is Yes or No, I do not see any message. Attaching the screenshot as I am not sure if we can upload file. Thanks.

 

[BMD44]

To add, the second scenario works while I have my cursor on cell E2 when I change it to

'Private Sub Worksheet_SelectionChange(ByVal Target As Range)'

But, is there a way I see the message automatically when the value changes in the cell instead of coming up when we select the cell.

 

[Alex Blakenburg]

Do you by any chance have a formula in E2 ?
If so show us the formula.
A formula will not trigger a worksheet change event

 

[Micron]

Your code works for me as long as I type in a cell. So as Alex notes, a formula won't trigger it. AFAIK, neither will a combobox change and that's what I see in the pic. I suppose you need to use the combo change event for the second test.

 

[BMD44]

Do you by any chance have a formula in E2 ?
If so show us the formula.
A formula will not trigger a worksheet change event

Hi Alex,

E2 is a formula. It gives value as "Yes" or "No" based on the formula.

=IF(SUM(E6:E500)>50000,"Yes","No")

 

[BMD44]

Your code works for me as long as I type in a cell. So as Alex notes, a formula won't trigger it. AFAIK, neither will a combobox change and that's what I see in the pic. I suppose you need to use the combo change event for the second test.

Hi Micron,

The cell that I am referring to is a formula cell. Can you please let me know how can we use Combo change event.

 

[Alex Blakenburg]

Is the date in the range E6:E500 manually entered or are they also formulas ?
PS: Your initial post say 500 not 50,000

 

[BMD44]

Is the date in the range E6:E500 manually entered or are they also formulas ?
PS: Your initial post say 500 not 50,000

Hi Alex,

E6:E500 is the list of values which will be updated every month manually. The total of the values should be greater than 50,000. To keep it simple, I have given the total as 500 in m initial post. The actual limit is 50,000.