I would like a row shaded based upon one of two values in column B. The
number of rows will change from worksheet to worksheet and my preference is
to use VBA as opposed to conditional formatting. Any suggestions?

Dim lastrow as Long, i as Long
lastrow = cells(rows.count,1).End(xlup)
for i = 1 to lastrow
if cells(i,"B").Value < 3 then
rows(i).Interior.ColorIndex = 3
elseif cells(i,"B").Value 10 then
rows(i).Interior.ColorIndex = 5
else
rows(i).Interior.ColorIndex = xlNone
end if
Next

--
Regards,
Tom Ogilvy

"briank" wrote in message
...
I would like a row shaded based upon one of two values in column B. The
number of rows will change from worksheet to worksheet and my preference
is
to use VBA as opposed to conditional formatting. Any suggestions?

Thanks Tom. Your code works great.
Regards, Brian

"Tom Ogilvy" wrote:

Dim lastrow as Long, i as Long
lastrow = cells(rows.count,1).End(xlup)
for i = 1 to lastrow
if cells(i,"B").Value < 3 then
rows(i).Interior.ColorIndex = 3
elseif cells(i,"B").Value 10 then
rows(i).Interior.ColorIndex = 5
else
rows(i).Interior.ColorIndex = xlNone
end if
Next

--
Regards,
Tom Ogilvy

"briank" wrote in message
...
I would like a row shaded based upon one of two values in column B. The
number of rows will change from worksheet to worksheet and my preference
is
to use VBA as opposed to conditional formatting. Any suggestions?

Hi Tom,

I've tried to use your code (modified for my sheet) and I've become
stuck:

Sub highlightrow()

Dim lastrow As Long, i As Long
lastrow = Cells(Rows.Count, 1).End(xlUp)
For i = 1 To lastrow
If Cells(i, "F").Value AND(=5,=<7) Then
Rows(i).Interior.ColorIndex = 3
ElseIf Cells(i, "F").Value 10 Then
Rows(i).Interior.ColorIndex = 5
Else
Rows(i).Interior.ColorIndex = xlNone
End If
Next

End Sub

This gets a 'Compile error; Expect: expression)

which leads to

-Expected: Expression. For example, when pasting code from the Object
Browser, you may have forgotten to specify a value for a named
argument.
Workbook.Add (X:= ) ' Error because no value assigned to
' named argument.

Either add a value for the argument, or delete the argument if it's
optional. -

I thought it would be just a matter of changing th <3 to AND(=5,=<7)
to make the condition between 2 values but obviously that's not the
case here.

I would be grateful if you could you show me where I've gone wrong and
why please.

Thank you


--
Daminc
------------------------------------------------------------------------
Daminc's Profile: http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=465603

I've managed to get rid of the red text by converting the code to:

Sub highlightrow()

Dim lastrow As Long, i As Long
lastrow = Cells(Rows.Count, 1).End(xlUp)
For i = 1 To lastrow
If Cells(i, "F").Value(i, "F" = 5 And i, "F" <= 7) Then
Rows(i).Interior.ColorIndex = 3
ElseIf Cells(i, "F").Value(i, "F" 7) Then
Rows(i).Interior.ColorIndex = 5
Else
Rows(i).Interior.ColorIndex = xlNone
End If
Next

End Sub

but now I get a Type Mismatch (Error 13) for line
lastrow = Cells(Rows.Count, 1).End(xlUp)

Any ideas

--
Damin
-----------------------------------------------------------------------
Daminc's Profile: http://www.excelforum.com/member.php...fo&userid=2707
View this thread: http://www.excelforum.com/showthread.php?threadid=46560

Maybe you want something like:

Option Explicit

Sub highlightrow()

Dim lastrow As Long, i As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To lastrow
If (Cells(i, "F").Value = 5 _
And Cells(i, "F").Value <= 7) Then
Rows(i).Interior.ColorIndex = 3
ElseIf Cells(i, "F").Value 10 Then
Rows(i).Interior.ColorIndex = 5
Else
Rows(i).Interior.ColorIndex = xlNone
End If
Next i

End Sub




Daminc wrote:

I've managed to get rid of the red text by converting the code to:

Sub highlightrow()

Dim lastrow As Long, i As Long
lastrow = Cells(Rows.Count, 1).End(xlUp)
For i = 1 To lastrow
If Cells(i, "F").Value(i, "F" = 5 And i, "F" <= 7) Then
Rows(i).Interior.ColorIndex = 3
ElseIf Cells(i, "F").Value(i, "F" 7) Then
Rows(i).Interior.ColorIndex = 5
Else
Rows(i).Interior.ColorIndex = xlNone
End If
Next

End Sub

but now I get a Type Mismatch (Error 13) for line
lastrow = Cells(Rows.Count, 1).End(xlUp)

Any ideas?

--
Daminc
------------------------------------------------------------------------
Daminc's Profile: http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=465603

--

Dave Peterson