Shading Rows
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? |
Shading Rows
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? |
Shading Rows
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? |
Shading Rows
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 |
Shading Rows
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 |
Shading Rows
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 |
Shading Rows
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 |
Shading Rows
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 |
Shading Rows
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 |
Shading Rows
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 |
Shading Rows
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 |
Shading Rows
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 |
Shading Rows
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 |
All times are GMT +1. The time now is 03:30 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com