Colouring any cell in worksheet with negative value

[Shivam.Shah]

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

 

[Dave Peterson]

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]

Thanks again!!

First, if you're not using format|conditional formatting, then this wouldbe 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