M
Matthew Dyer
Im running into some issues and I have an idea as to what the problem
is but I have no idea how to solve it. The following code will
highlight any rows that have a column F value >= RollVal. I tried to
modify it to also add up all the values of column G if the value of
column F and return this value in cell J2, but my coding skills seem
to be severely lacking. I think the problem is that it is trying to
include the values of the header row, which is obviously not currency
or any numerical value at all. Would making the objRow start with Row
2 fix this? Here is the code:
Sub RollCalc()
Dim RollVal As Long
Dim objRow As Object
Dim TotalRoll As Currency
Dim Bal As Currency
RollVal = Application.InputBox("What is the Roll DPD?", _
"Get Roll DPD Value ", Type:=1)
For Each objRow In ActiveSheet.UsedRange.Rows
If Cells(objRow.Row, "F").Value >= RollVal Then
objRow.Interior.ColorIndex = 45
Bal = Cells(objRow.Row, "G").Value
TotalRoll = TotalRoll + Bal
Else
objRow.Interior.ColorIndex = xlNone
End If
Next objRow
Range("J2").FormulaR1C1 = TotalRoll
End Sub
is but I have no idea how to solve it. The following code will
highlight any rows that have a column F value >= RollVal. I tried to
modify it to also add up all the values of column G if the value of
column F and return this value in cell J2, but my coding skills seem
to be severely lacking. I think the problem is that it is trying to
include the values of the header row, which is obviously not currency
or any numerical value at all. Would making the objRow start with Row
2 fix this? Here is the code:
Sub RollCalc()
Dim RollVal As Long
Dim objRow As Object
Dim TotalRoll As Currency
Dim Bal As Currency
RollVal = Application.InputBox("What is the Roll DPD?", _
"Get Roll DPD Value ", Type:=1)
For Each objRow In ActiveSheet.UsedRange.Rows
If Cells(objRow.Row, "F").Value >= RollVal Then
objRow.Interior.ColorIndex = 45
Bal = Cells(objRow.Row, "G").Value
TotalRoll = TotalRoll + Bal
Else
objRow.Interior.ColorIndex = xlNone
End If
Next objRow
Range("J2").FormulaR1C1 = TotalRoll
End Sub