ExcelBanter - pulling values from column g into a variable, and returning variableon spreadsheet

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - pulling values from column g into a variable, and returning variableon spreadsheet (https://www.excelbanter.com/excel-programming/434606-pulling-values-column-g-into-variable-returning-variableon-spreadsheet.html)

pulling values from column g into a variable, and returning variableon spreadsheet

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

pulling values from column g into a variable, and returning variable on spreadsheet

pulling values from column g into a variable, and returningvariable on spreadsheet

On Oct 6, 12:09*pm, "Don Guillett" wrote:
Sub hiliteif()
Rows.Interior.ColorIndex = 0
rollval = Application.InputBox("What is the Roll DPD?", _
* * "Get Roll DPD Value ", Type:=1)

For i = 2 To Cells(Rows.Count, "f").End(xlUp).Row
If Cells(i, "f") rollval Then
Rows(i).Interior.ColorIndex = 45
ms = ms + Cells(i, "g")
End If
Next i
Range("J2").Value = ms
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"Matthew Dyer" wrote in message

...

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- Hide quoted text -

- Show quoted text -

This works fantastic! One request though... I really liked the
origional code is that it would only highlight the used range of
cells. Using this code, how do I stop highlighting at the end of my
range of columns? If it helps my range ends at column H. I tried the
following change, but it didnt work:

If Cells(i, "f") = RollVal Then
Rows(i, "H").Interior.ColorIndex = 45

I get an error saying "Application-defined or object-defined error

The best I could do for now is to just highlight one specific column
with the following code:

If Cells(i, "f") = RollVal Then
Cells(i, "F").Interior.ColorIndex = 45

I also tried to do Cells(i, "A:H").Interior... but that didnt work
either.

pulling values from column g into a variable, and returning variable on spreadsheet