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

Excellent Dave. That works perfectly. :)

Is there any chance that you might explain where I went wrong and why
it was wrong (just to stop me from making the same mistake twice)

Thanks.


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

There's a learning curve with each language. You have to learn how to write
your If statements.

If Cells(i, "F").Value AND(=5,=<7) Then

This looks like you tried to use excel's worksheet function =and().

In VBA, you have to use its rules.

If (Cells(i, "F").Value = 5 _
And Cells(i, "F").Value <= 7) Then

You may want to invest in a book.

Debra Dalgleish has a list at her site:
http://www.contextures.com/xlbooks.html

John Walkenbach's is a nice one to start with. See if you can find them in your
local bookstore and you can choose what one you like best.

Daminc wrote:

Excellent Dave. That works perfectly. :)

Is there any chance that you might explain where I went wrong and why
it was wrong (just to stop me from making the same mistake twice)

Thanks.

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

Cheers Dave,

'Excel VBA Macro Programming' by Richard Shepard
is the book I'm in the process of getting. Have you come across thi
book before

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

Nope. I haven't heard good or bad things.

Maybe someone who owns it can chime in.

Daminc wrote:

Cheers Dave,

'Excel VBA Macro Programming' by Richard Shepard
is the book I'm in the process of getting. Have you come across this
book before?

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

Hi Dave, I'm very sorry I didn't reply to you it was rude of me. Your
coding worked perfect. It was also simple enough to be altered for
other uses as well :)


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

Glad you got it working.

Daminc wrote:

Hi Dave, I'm very sorry I didn't reply to you it was rude of me. Your
coding worked perfect. It was also simple enough to be altered for
other uses as well :)

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

I haven't long finished 'Excel VBA Macro Programming' by Richard
Shepard.

For what it's worth I would recommend this book to people starting VBA.
It's one that I plan to keep as a reference book at least until I'm able
to memorise the stuff :)


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