validations code for negative and changed values

[rajraj9876]

I have a requirement I have to write a VBA code to validate for all excel cell with negative values. In case the user enters a negative value, a message box thrown " No negative values allowed" and secondly if the user changes the value of a cell that
particular cell should become red color. Again on refresh the red color validation should disappear. I have a sample code which is giving an error

(Macro 1 - For negative value validation)

Sub Worksheet_Change(ByVal Target As Range)
If Range("A1") < 0 Then
MsgBox "No Negative value"
End If
Target.Font.ColorIndex = 3
End Sub

(Macro 2 - For changed cell)


Private Sub Worksheet_Change(ByVal Target as Range)
Target.Font.ColorIndex = 5
End Sub

[Jacob Hilderbrand]

I tried both subs and did not receive an error.

For the data restrictions have a look at Data Validation, you can require a number and give it a range, and adjust the message that is displayed when invalid data is entered.

For the coloring you can use Conditional Formatting to apply different formats based on the cell's value.

In the case of the first macro you are only looking at A1 regardless of what was changed. Target is the range you can check, however if you are checking for values you need to check each cell in the range:




Sub Worksheet_Change(ByVal Target As Range)

Dim Cel As Range

For Each Cel In Target
If Cel.Value < 0 Then
MsgBox "No Negative value"
End If
Next
Target.Font.ColorIndex = 3

End Sub