I have a pivot table that is produced via VBA from MC Access into a worksheet in Excel. This is all working fine and once the pivot in created, I copy it over itself and then format. The part of the formatting I can not get working is to change the row color with each change in vlaue in colmun A of the range of the original pivot table (row 5 to row 10). I determine the pivot range before I copy it, so when i get to this part of the code the range is known (myRange2), and I set the starting row + 2 to ignore the header rows. The data also includes blanks as a result of the pivot so I am trying to use variables to check if the value has changed or is it blank. Where it is blank, keep formating the current row color, if it has changed switch to other color, looping through this logic effectivly creating a banded color report.
The image shows the desire format in rows 7 to 10, but also that it is instead of starting on row seven, it is starting 7 rows below the range (row 17) and loops 4 times creating 4 gray and white bands.
At this point I can't see the woods for the trees and would really appreciate a fresh pair of eyes on this.
Dim firstRow As Long
Dim lastRow As Long
Dim lRow As Long
Dim cellVal1, cellVal2 As String
'myRange 2 has already been populated in prior code
'Set color banding for change in role name
firstRow = .Cells(1).Row + 2
lastRow = .Cells.Rows(.Cells.Rows.Co
For lRow = firstRow To lastRow Step 1
With .Cells(lRow, "A")
If Not IsError(.Value) Then
cellVal1 = .Value
If .Value = "" Then 'Or .Value = cellVal1 Then
cellVal1 = cellVal2
= RGB(191, 191, 191)
= RGB(255, 255, 255)
cellVal2 = cellVal1