highlight rows based on value of column F

[Matthew Dyer]

Here's what I have so far. Of course it isn't working properly, but I
think it should be an easy fix. I have a input box pop up requesting a
numerical value. Then, if the value in column F is >= this value, it
highlights the row as yellow.

Sub RollCalc()
Dim RollVal As Long
RollVal = Application.InputBox("What is the Roll DPD?", "Get Roll DPD
Value", Type:=1)

With Row
If .Column(F).Value >= RollVal Then Row.ColorIndex = 6
End
End With
End Sub

Is my With Row part just terrible from beginning to end? Any help?

 

[OssieMac]

Hello Matthew,

Try the following. Note that a space and undersocre at the end of a line is
a line break in an otherwise single line of code.

Also I have included lines with the single quote (commented out) for
coloring the font instead of the background. If you want the font colored
then use these lines and comment out the interior lines.

Code also removes the color for lines that do not meet the criteria.

Sub RollCalc()

Dim RollVal As Long
Dim objRow As Object

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
Cells(objRow.Row, "F") _
.EntireRow.Interior.ColorIndex = 6

'Cells(objRow.Row, "F") _
.EntireRow.Font.ColorIndex = 6
Else
Cells(objRow.Row, "F") _
.EntireRow.Interior.ColorIndex = xlNone

'Cells(objRow.Row, "F") _
.EntireRow.Font.ColorIndex = xlNone
End If
Next objRow

End Sub

 

[FSt1]

hi
are you trying to do one row at a time or the whole F column??
your code is confusing and yes there are problems with the with clause.
i supplied two(2) subs, one for one row and one for all rows.
if one row at a time use this to key on the activecell's row.......
Sub RollCalc()
Dim RollVal As Long
Dim ar As Long
ar = ActiveCell.Row
RollVal = InputBox("What is the Roll DPD?", _
"Get Roll DPD Value ")
If Range("F" & ar).Value >= RollVal Then
Rows(ar).Interior.ColorIndex = 6
End If
End Sub

if you are trying to do all at once, use this loop.....
Sub RollCalcII()
Dim RollVal As Long
Dim ar As Long
ar = Cells(Rows.Count, "F").End(xlUp).Row
RollVal = InputBox("What is the Roll DPD?", _
"Get Roll DPD Value ")
For Each cell In Range("F2:F" & ar)
If cell >= RollVal Then
Rows(cell).Interior.ColorIndex = 6
End If
Next cell
End Sub

regards
FSt1

 

[OssieMac]

Hello again Matthew,

The code I posted can be significantly simplified in the lines for
colorindex. I must have been off the planet for a while when I wrote it.

Sub RollCalc2()

Dim RollVal As Long
Dim objRow As Object

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 = 6

'objRow.Font.ColorIndex = 6
Else
objRow.Interior.ColorIndex = xlNone

'objRow.Font.ColorIndex = xlNone
End If
Next objRow

End Sub

 

[Matthew Dyer]

Ossie, your code works perfectly. How would I modify it so the header
row isn't highlighted as well? Aparently any text is >= any numerical
value.

FSt1, I tested your code as well. The first code worked perfectly for
testing the specific row that was 'active', but the second code worked
in highlighting the row numbers instead of comparing the value of
column F to RollVal.

 

[OssieMac]

Hi again Matthew,

My apologies. Should have thought of that myself.

Sub RollCalc2()

Dim RollVal As Long
Dim objRow As Object

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 _
And objRow.Row <> 1 Then

objRow.Interior.ColorIndex = 6

'objRow.Font.ColorIndex = 6
Else
objRow.Interior.ColorIndex = xlNone

'objRow.Font.ColorIndex = xlNone
End If
Next objRow

End Sub