CODEWITHSUNDEEP
excelvba
Xin
Xin

Reputation: 674

How to get row sum for multiple rows?

enter image description here

I am trying to change the cells under B column to blue if the sum for the corresponding row is less than 55000. Below is my code I have figured out to achieve that for one row. How could I modify it so that it works for the other rows if I have a lot of rows?

    Dim rng As Range
    Dim result As Long

    Set rng = Sheets(2).Range("C2:N2")

    result = Application.WorksheetFunction.Sum(rng)
    
    If result < 550000 Then
        Sheet2.Range("B2").Font.Color = vbBlue
        Sheet2.Range("B2").Font.Bold = True
    End If

Upvotes: 0

Views: 57

Answers (1)

BigBen
BigBen

Reputation: 49998

With a loop:

With Sheet2
    Dim lastRow As Long
    lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

    Dim i As Long
    For i = 2 To lastRow
        If Application.Sum(.Range("C" & i & ":N" & i)) < 550000 Then
            .Cells(i, "B").Font.Color = vbBlue
            .Cells(i, "B").Font.Bold = True
        End If
    Next
End With

EDIT: If you want to do the same thing, but for columns instead of rows:

With Sheet2
    Dim lastColumn As Long
    lastColumn = .Cells(1, .Columns.Count).End(xlToRight).Column

    For i = 3 To lastColumn
        If Application.Sum(.Columns(i)) < 550000 Then
            .Cells(1, i).Font.Color = vbBlue
            .Cells(1, i).Font.Bold = True
        End If
    Next
End With

Upvotes: 1

Related Questions