Shading Rows

[briank]

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?

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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?

[briank]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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?
>
>
>

[Daminc]

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]

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?

[Dave Peterson]

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

[Daminc]

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.

[Dave Peterson]

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

[Daminc]

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?

[Dave Peterson]

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

[Dave Peterson]

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

[Daminc]

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