Row shading changes when value changes

Topic

New Reply

Good day Loungers!
I have a bit of a puzzle that I want to pose.

I have a workbook that contains employee ID #’s and other info about that employee. Each employee may have 1 or more rows. I would like to change the shading of the row each time the employee # changes.

For example, employee #1 has 1 row so I want that row to remain unshaded. Employee 2 has 5 rows and I want all 5 of those rows to be shaded, Emplyoee 3 has 2 rows and they should be unshaded etc…

I have an add-in (ShadeDataRows.xla) that I downloaded that works, but I really want to incorporate this process into another macro that I am running on the same data.

I have attached a sample workbook that shows the results as I want them .

TIA,
Greg

Reply | Quote

Replies

Try this:

Code:

Sub shading()
Dim i As Long, lrow As Long, iShade As Integer
Dim bolShade As Boolean

bolShade = True
lrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lrow
	If Cells(i, 1)  Cells(i - 1, 1) Then
 	bolShade = Not bolShade
 	If bolShade = False Then
 	With Range("A" & i & ":E" & i)
 	.Interior.ColorIndex = 15
 	End With
 	Else
 	With Range("A" & i & ":E" & i)
 	.Interior.ColorIndex = -4142
 	End With
 	End If
	Else
 	With Range("A" & i & ":E" & i)
 	.Interior.ColorIndex = Cells(i - 1, 1).Interior.ColorIndex
 	End With
	End If
Next
End Sub

Reply | Quote

This works great. Thank you!

Is there any way to make this adaptable for the ending column? The last column is not always E, but could be different for the rows. The sample I sent had most of the coulmns trimmed out to remove sensitive data.

tia,
Greg

Reply | Quote

This should do it

Code:

Sub shading()
Dim i As Long, lrow As Long, iShade As Integer, lCol As Long
Dim bolShade As Boolean

bolShade = True
lCol = Cells(1, Columns.Count).End(xlToLeft).Column
lrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lrow
	If Cells(i, 1)  Cells(i - 1, 1) Then
 	bolShade = Not bolShade
 	If bolShade = False Then
 	With Range(Cells(i, 1), Cells(i, lCol))
 	.Interior.ColorIndex = 15
 	End With
 	Else
 	With Range(Cells(i, 1), Cells(i, lCol))
 	.Interior.ColorIndex = -4142
 	End With
 	End If
	Else
 	With Range(Cells(i, 1), Cells(i, lCol))
 	.Interior.ColorIndex = Cells(i - 1, 1).Interior.ColorIndex
 	End With
	End If
Next
End Sub

Reply | Quote

Here is a variant using conditional formatting.

Reply | Quote

Perfect!
Thanks

Reply | Quote

The conditional formatting method will work if there are blanks between the ID#s. The sheet in question has repeating values in the ID# field. Using the CF method with gvanhook’s sheet will result in alternating rows being shaded.

Reply | Quote

I meant that it could be used as a jumping off point if one wanted a Conditional Formatting solution.
Here is a CF solution. I admit it would be more elegant if one could avoid adding the additional columns used. However, the record count field is often quite useful and not only for formatting.

Reply | Quote