CODEWITHSUNDEEP
vbaexcelnegative-number
Trung Tran
Trung Tran

Reputation: 13721

vba check for negative value

I wrote a simple VBA code to check if the value of a cell is negative and if it is negative, highlight it red. For some reason, I keep getting "run-time mismatch". My code is

For x = 2 To 100

Set val3 = Worksheets("Summary").Cells(x, 9)
If val3.Value < 0 Then
Worksheets("Summary").Cells(x, 9).FontColorIndex = 3

End If
Next x

Column 9 (the column I am checking) is filled with dollar values. Thank you in advance for your help.

Upvotes: 0

Views: 37580

Answers (3)

bennyhardjono
bennyhardjono

Reputation: 51

Thanks for your post, I needed to change the negative numbers to zeros after a certain process - BUT I want to check them before I use them so changing the font color is useful, so this code works well for the whole sheet range until (245,50)

Sub negnumbers()
    For x = 1 To 245
        For y = 1 To 50
        Set val3 = Worksheets("target sheet").Cells(x, y)
        If val3.Value < 0 Then
        Worksheets("target sheet").Cells(x, y).Font.ColorIndex = 3
        Worksheets("target sheet").Cells(x, y) = 0
        End If
        Next y
    Next x
End Sub

Upvotes: 0

timbur
timbur

Reputation: 76

In your code, you're simply missing a dot.

FontColorIndex = 3

should be:

Font.ColorIndex = 3

Upvotes: 6

elrado
elrado

Reputation: 5272

Public Sub test()
   For x = 2 To 100
      Set val3 = Worksheets("Sheet1").Cells(x, 9)
      If val3.Value < 0 Then
         Worksheets("Sheet1").Cells(x, 9).Font.Color = RGB(99, 179, 73)
      End If
   Next x
End Sub

Code above works in excel 2007

Upvotes: 0

Related Questions