How to sum up columns in each row and highlight until that value

< Back to Search results

 Category: [Excel]  Demo Available 

How to sum up columns in each row and highlight until that value



The user has a database with more than 100 columns and 1000 rows with each row have items name and column is inventory number. The user wants to sum up columns in each row and highlight until match or less than value number in the first column (cannot be larger than that number).




Option Explicit
Sub MatchValues()
    Dim i As Long, n As Long, db, j As Long
    Dim ValueA As Long
    Dim lrA As Long
    Dim lrow As Long
    Dim sht As Worksheet
    Set sht = Worksheets("Sheet2")
    lrA = sht.Cells(1, Columns.Count).End(xlToLeft).Column
    lrow = sht.Cells(Rows.Count, 1).End(xlUp).Row
    i = 2
    db = 2
    For i = 2 To lrow
        n = 1
        ValueA = 0
            n = n + 1
            ValueA = ValueA + sht.Cells(i, n).Value
            If IsNumeric(sht.Cells(i, n).Value) And Not sht.Cells(i, n).Value = vbNullString Then db = db & "," & n
        Loop Until ValueA >= sht.Cells(i, 1).Value Or n = lrA + 2

        j = Split(db, ",")(UBound(Split(db, ",")) - IIf(ValueA = sht.Cells(i, 1).Value, 0, 1))
        sht.Range(Cells(i, 2), Cells(i, j)).Interior.Color = vbYellow
    Next i
End Sub


Obtained from the OzGrid Help Forum.

Solution provided by pike.


See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets


See also:


How to use SUMPRODUCT with INDEX MATCH multiple criteria
How to set up a pop up message when sum of columns exceeds X
How to use VBA to SUM result in last row in columns A:A
How to merge duplicate rows and sum value
How to use SUMIFS and include 'All' to Drop-down IndexMatch


Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.


stars (0 Reviews)