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

2. Try this:
Code:
```Sub shading()
Dim i As Long, lrow As Long, iShade As Integer

lrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lrow
If Cells(i, 1) <> Cells(i - 1, 1) 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```

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

4. This should do it

Code:
```Sub shading()
Dim i As Long, lrow As Long, iShade As Integer, lCol As Long

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

5. Here is a variant using conditional formatting.[attachment=86837:Custom Formatting for Rebecca.xls]

6. Perfect!
Thanks

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

8. [attachment=86855:counting variable numbers of records 2.xlsx]
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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•