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

[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

 

[Don Guillett]

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

 

[Matthew Dyer]

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












- 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.

 

[Don Guillett]

When replying to me please TOP post so I don't have to look for your reply.

Cells(i, 1).Resize(, 8).Interior.ColorIndex = 45

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

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
message












- 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.