Sub for Lowest Negative

Hello,

I am trying to write a procedure in VBA for excel to find the lowest negative value from a selected range. If there are no negative values, then a message box should display "There are no negative values" Below is what I have so far, but I'm not sure if it si correct or how to compare each case to the previous. Thanks for any help.

Sub LowestNegative()

Dim Lowest As Single

For Each c In Selection.Cells
Select Case c.Value
    Case Is < 0
    Lowest = ActiveCell.Value
    Case Is > 0
    Lowest = MsgBox("There are no negative values.")
    Case Else
    Lowest = MsgBox("There are no negative values.")
End Select
Next


End Sub

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Before exploring your code, why not simply use the =MIN() function in a spreadsheet cell, or accessed in VBA (see help file here, https://msdn.microsoft.com/en-us/lib.../ff197608.aspx with an example that uses the MIN() function)?

I tend to use the .Match() function for this. See the other example in the help file.

The Match() function will tell you what row the lowest number is in, then you can use the .select or .activate method to activate a cell in that row, or you can use some other means (change font or background color) to highlight one or more cells in that row. This help file describes the .interior property and shows how to use it to change the cell color: https://msdn.microsoft.com/en-us/lib.../ff836210.aspx