Colouring any cell in worksheet with negative value

First, if you're not using format|conditional formatting, then this would be a
good use for it. You'll find it much easier to implement, it won't destroy the
undo/redo stack and it'll react to both user changes and formula recalculations.

But if you want to use that worksheet_change event:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const ColumnsToCheck As String = "A:BB"

If Target.Cells.Count > 1 Then
Exit Sub 'single cell at a time
End If

If Intersect(Target, Me.Range(ColumnsToCheck)) Is Nothing Then
'do nothing
Else
If Target.Value < 0 Then
Target.Interior.ColorIndex = 3
End If
End If

End Sub



"Shivam.Shah" wrote:
>
> Hi all,
>
> I have written the below code for making the cells from column to A to
> BB fill with red colour, if they have a negative value in them.
>
> I can't seem to make it work.....Any help will be appreciated! Thanks
> very much!
>
> Shivam
>
> ******************************
> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> Const ColumnsToCheck As String = "A:BB"
> If Not Intersect(Target, Range(ColumnsToCheck)) Is Nothing Then
> If Target.Value < 0 Then
> Target.Value.Select
> Selection.Interior.ColorIndex = 3
> End If
> End If
> End Sub
 >> Stay informed about: Colouring any cell in worksheet with negative value