How to sum up columns in each row and highlight until that value
Category: [Excel] Demo Available
How to sum up columns in each row and highlight until that value
Requirement:
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).
Solution:
Code:
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
Do
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.