CODEWITHSUNDEEP
vba
user2617199
user2617199

Reputation: 3

VBA code to color cells having negative values

The below code runs successfully, however, I need a code that treats both positive and negative cell values as same and colors it accordingly

Ex:

  1. 0 to 4 and 0 to -4 = Green color
  2. 5 to 9 and -5 to -9 = Orange color

The below code works only for positive values

Sub changeTextColor()

    GreenColor = RGB(0, 128, 0)
    RedColor = RGB(255, 0, 0)
    OrangeColor = RGB(255, 204, 0)
    WhiteColor = RGB(255, 255, 255)
    
    'Get number of rows in the specified column
    RowsCount = Range("K2", Range("K2").End(xlDown)).Rows.Count
    
    'Select cell
    Range("K2").Select
    
    'Loop the cells
    For x = 1 To RowsCount
        If ((ActiveCell.Value) <= 4) Then
            ActiveCell.Interior.Color = GreenColor
        ElseIf ((ActiveCell.Value) >= 5) And ((ActiveCell.Value) <= 9) Then
            ActiveCell.Interior.Color = OrangeColor
        ElseIf ((ActiveCell.Value) > 10) And ((ActiveCell.Value) <= 10000) Then
            ActiveCell.Interior.Color = RedColor
        
        End If
    
        ActiveCell.Offset(1, 0).Select
       
        
    Next
    
End Sub

Upvotes: 0

Views: 3852

Answers (1)

KekuSemau
KekuSemau

Reputation: 6854

Use the absolute value.
Replace the conditions like (ActiveCell.Value) <= 4) with (Abs(ActiveCell.Value) <= 4).

Code like this will run much faster when you do not use 'Select', just refer directly to the Cells.

Dim v As Long
Dim r As Range
Dim i As Long

' [...]

'Select cell -> NO, don't
' Range("K2").Select

'Loop the cells
For x = 1 To RowsCount
    Set r = ActiveSheet.Cells(1+x, "K") ' starts at K2
    v = Abs(r.Value)

    If v <= 4 Then
        r.Interior.Color = GreenColor
    elseif ... ' and so on
    ' ...

Next

And btw, take a look at Conditional Formatting.

Upvotes: 1

Related Questions