Search a column find a value and create a message box

[ckmacaus]

Good evening, I have a question that I am sure will be simple for many; I was provided this to search a sheet in excel and find a value less than zero and create a msgbox.

Private Sub Worksheet_Calculate()
If Me.Range("$K$14").Value<0 Then_ MsgBox "Value can not be negative"
End Sub

This works fine for cell K14 but I want to apply it to all cells K14 to K54 on the sheet. Can you suggest anything? I have tried a number of things and searched for answers but I can not find the right fix.

Thank you for any suggestions in advance.

 

[Rick Rothstein]

Good evening, I have a question that I am sure will be simple for many; I was provided this to search a sheet in excel and find a value less than zero and create a msgbox.

Private Sub Worksheet_Calculate()
If Me.Range("$K$14").Value<0 Then_ MsgBox "Value can not be negative"
End Sub

This works fine for cell K14 but I want to apply it to all cells K14 to K54 on the sheet. Can you suggest anything? I have tried a number of things and searched for answers but I can not find the right fix.

I think I would simply turn the negative cells within that range red using conditional formatting instead.

 

[DanteAmor]

If you want to check it with macro and in the first negative cell you find stop macro

Private Sub Worksheet_Calculate()
    For Each wCell In Range("G14:K54")
        If wCell.Value < 0 Then
            MsgBox "Value can not be negative. Cell : " & wCell.Address
            Exit Sub
        End If
    Next
End Sub

Or if you want all the negative cells in a single message

Private Sub Worksheet_Calculate()
    For Each wcell In Range("G14:K54")
        If wcell.Value < 0 Then
            cad = cad & wcell.Address & ", "
        End If
    Next
    If cad <> "" Then
        MsgBox "Value can not be negative. Cell : " & cad
    End If
End Sub

Or with a simple cell format: Format Cell / Custom / Type:


#, ## 0.00; [Red] - #, ## 0.00