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

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
 
D

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
 
M

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top