PDA

View Full Version : [SOLVED] VBA to Highlight Rows based upon a Populated Cell



Slicemahn
01-16-2015, 01:51 PM
Using Excel 2007

Hey Everyone,


I need a second pair of eyes to help me find out what is wrong with my code to highlight a row based upon a populated cell.
Essentially what I am doing is looping through the data to find the cell in which is populated (Column U) and then from that cell go down to the next free or non-populated cell and then highlight that entire row (similar to xlDown.Row action).

Here is my code....


Sub HighlightIt()
Dim EndRow As Long
Dim RowStepper As Integer
Dim SummaryRow As Integer
Dim ws As Worksheet
Application.ScreenUpdating = False
Set ws = Sheets("LearningPlanItems")
With ws
EndRow = .Cells(65536, 1).End(xlUp).Row
EndRow = EndRow - 1
For RowStepper = EndRow To 5 Step -1
If ws.Cells(RowStepper, 21).Value Is Not Null Then
SummaryRow = ws.Cells(RowStepper, 21).End(xlDown).Row
ws.Rows(SummaryRow).EntireRow.Interior.ColorIndex = 28
End If
SummaryRow = 0
Next RowStepper
Application.ScreenUpdating = True
End With

End Sub

I get an object error each time I try to execute this code.

Thanks.

p45cal
01-16-2015, 03:49 PM
try:
Sub HighlightIt()
Dim EndRow As Long
Dim RowStepper As Integer
Dim SummaryRow As Long
Dim ws As Worksheet
Application.ScreenUpdating = False
Set ws = Sheets("LearningPlanItems")
With ws
EndRow = .Cells(65536, 1).End(xlUp).Row
EndRow = EndRow - 1
For RowStepper = EndRow To 5 Step -1
If Len(.Cells(RowStepper, 21).Value) > 0 Then
SummaryRow = .Cells(RowStepper, 21).End(xlDown).Row
.Rows(SummaryRow).EntireRow.Interior.ColorIndex = 28
End If
SummaryRow = 0
Next RowStepper
Application.ScreenUpdating = True
End With
End Sub

SamT
01-16-2015, 04:51 PM
Please use the # button in the editor menu to format your code if you don't usually format it as you code.
You have declared several variables as integers, when they might have to hold values larger than any integer.
LastRow (EndRow) should be encoded like

LastRow = Cells(Rows.Count, n). End(xlUp).Row 'plus any math
Under certain circumstances EndRow = EndRow - 1 can cause a crash. In your case the Safety is

EndRow = EndRow - 1
If EndRow < 6 then Exit Sub


As to the Object error, You should never trust that the value of an apparently empty Excel Cell is Null.

Use
Value = ""
'or
Value<> ""

Also try this code

Sub X()
Dim Cel

With Sheets("LearningPlanItems")
Set Cel = Range("U5")
Do
Set Cel = Cel.End(xlDown)
If Cel.Row = Rows.Count Then Exit Sub
If Cel.Value <> "" And Cel.Offset(1).Value <> "" Then _
Cel.EntireRow.Interior.ColorIndex = 28
Loop
End With
End Sub

Paul_Hossler
01-17-2015, 03:48 PM
Slight tweak to p45cal's code




Option Explicit
Sub HighlightIt()
Dim EndRow As Long
Dim RowStepper As Long
Dim SummaryRow As Long
Dim ws As Worksheet

Application.ScreenUpdating = False

Set ws = Sheets("LearningPlanItems")

With ws
EndRow = .Cells(.Rows.Count, 1).End(xlUp).Row - 1

For RowStepper = EndRow To 5 Step -1
If Len(.Cells(RowStepper, 21).Value) > 0 Then
SummaryRow = .Cells(RowStepper, 21).End(xlDown).Row
.Rows(SummaryRow).EntireRow.Interior.ColorIndex = 28
End If

SummaryRow = 0
Next RowStepper

Application.ScreenUpdating = True
End With
End Sub

Slicemahn
01-19-2015, 06:43 AM
Thanks p45cal! I am stuck in SQL scripts thus Is Not Nulls and so forth.

Slicemahn
01-19-2015, 06:45 AM
Thanks Paul! Help me refresh my dimensioning: Do I need to dimension RowStepper and SummaryRow as Long?

Slicemahn
01-19-2015, 06:48 AM
Sorry Sam I didn't see your response until the end. You're right I should have error traps to exit the code. It is has been a while since I have done VBA so this is a great refresher. I been doing mostly T-SQL. So I ask that the community bear with me as I become acclimated with VBA scripting once again.

Thanks Everyone!
Slice