VBA Conditional Query

VBA Conditional Query

Hi,


could someone here please help with the following dilemma i'm having? I have zero skill with code but after reading a few posts here, ive pieced together the following VBA code to 'almost' achieve what i want.


Private Sub Worksheet_Change(ByVal Target As Range)
 Application.EnableEvents = false
 On Error Goto ENEV
 If Target.Column = 10 Then ' 10 means Column J
  If Target.value<0 then
       Target.Interior.Color = RGB(255,0,0) 'Red fill colour
  ElseIf Target.value>0 then
       Target.Interior.Color =  RGB(0,255,0) 'Green fill colour
  End If
 End If
 ENEV:
  Application.EnableEvents = true
End Sub












What id like to happen:

1) If any cell in a column (J) has a numeric value greater than zero, it gets a green fill.

2)  If any cell in a column (J) has a numeric value less than zero, it gets filled with red.

3) If the number is 0 or the cell is empty, the cell is not filled with any colour.

4) the bottom cell in column J that has the Sum of that column should obey the same fill rules as the cells.

5) If i delete or clear the contents of a cell, the fill colour should clear as well (without having to refill the cell with white).


I cant seem to get the cell with the sum to obey the rules. Is it because its a formula cell?

Would appreciate and help. Thanks for your time (and patience)  :)


Anthony