Change row color each time a value changes

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.

 pivot format example
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.

code except
    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
            With myRange2
                firstRow = .Cells(1).Row + 2
                lastRow = .Cells.Rows(.Cells.Rows.Count).Row
                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
                                .Rows(lRow).Interior.Color = RGB(191, 191, 191)
                            Else
                                .Rows(lRow).Interior.Color = RGB(255, 255, 255)
                            End If
                        End If
                        cellVal2 = cellVal1
                    End With
                Next lRow
            End With
shipleypAsked:
Who is Participating?
 
Robberbaron (robr)Connect With a Mentor Commented:
great that it works,

for what it's worth, i find this logic easier to find and slighly less if statements to be executed
and tests are for equality, which is generally better, because you can add more cases easily.

but i havent tested it to be sure it gives same results as your code,
With myrange2
        .Rows(3).Interior.Color = RGB(191, 191, 191)
       
        For lRow = 4 To .Rows.Count Step 1
        
            If .Cells(lRow, "A").Value = "" Then
                If .Rows(lRow - 1).Interior.ColorIndex = 15 Then
                    banded = False
                    cellcol1 = 191 ' grey
                 Else
                    banded = True
                    cellcol1 = 255 ' white
                End If
            Else
                '<>""
                If .Rows(lRow - 1).Interior.ColorIndex = 15 Then
                    banded = True
                    cellcol1 = 255 ' white

                 Else
                    banded = False
                    cellcol1 = 191 ' grey
                End If
            End If

            Select Case banded
                Case True
                    .Rows(lRow).Interior.Color = RGB(cellcol1, cellcol1, cellcol1)

                Case False
                    .Rows(lRow).Interior.Color = RGB(cellcol1, cellcol1, cellcol1)
       
            End Select
            
        Next lRow
    End With

Open in new window

0
 
aflockhartCommented:
You are using  WITH statements; are you sure that when you refer to cells inside these, that it is referring to the cells that you intend ?

e.g. you set "lRow" to the row number of the first row on which data is found. Initially set to 7.   But on the next line you refer to this:

With .Cells(lRow, "A")

Because you use ".cells" here, that is translated to

With MyRange2.cells(lrow,"a")  whcih refers to the seventh row couinting down from the starting point at the top of myrange2, ie row 11.

The cell on row 11 is the starting point of the inner WITH statement, so when you later use the statement:

.Rows(lRow).Interior.Color = RGB(191, 191, 191)


it counts 7 rows down from the starting point on row 11 to get to row 17, which it shades in grey.

Next time through the loop, the anchor point is calculated as row 12, and then counts down a  further 8 rows to get to row 19

etc


0
 
Robberbaron (robr)Commented:
to build on aflockhart comments, see my version

I have separated out the formatting from maincode to make it easier to incorporate in your project


note how the formatting is only applied to the columns A-D, as that is the size of the range passed.
Sub Main1()

    Dim myrange2 As Range
    
    Set myrange2 = ActiveSheet.Range("A5:D19")
    formatBands myrange2
    
End Sub

Sub formatBands(rangeValues As Range)

    
    Dim firstRow As Long
    Dim lastRow As Long
    Dim lRow As Long
    Dim cellVal1, cellVal2 As String

    For lRow = 2 To rangeValues.Rows.Count
        'this is from the start of range. so row 1 is first cell of range
        With rangeValues.Cells(lRow, "A")
            If Not IsError(.Value) Then
                cellVal1 = .Value
                If .Value = "" Then 'Or .Value = cellVal1 Then
                    cellVal1 = cellVal2
                    rangeValues.Rows(lRow).Interior.Color = RGB(191, 191, 191)
                Else
                    rangeValues.Rows(lRow).Interior.Color = RGB(255, 255, 255)
                End If
            End If
            cellVal2 = cellVal1
        End With
    Next lRow

    
End Sub

Open in new window

Q-27419758.xls
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
shipleypAuthor Commented:
Hi robberbaron,

I hav attached my full function code. myrange2 is previously determined to give the starting cell of the pivot table. So it should still be set and it will be different for each sheet in the workbook. The logic of the code is if my selection form has * for object loop through the number of objects per process area, and if the object has no transaction codes, don't paste in this list, but then still do the pivot. If the object selection is a specific value then build the transaction list, determine the start point of the pivot, generate the pivot and paste over itself, then format it.

Note: the formatting could be very wide, some of the sheets will end up with 500 columns, so it needs to be based upon the original range of each pivot table.

I put your code in, put i am still having trouble with it knowing the correct range to work with. TransExport.bas
PivotCode.bas

0
 
shipleypAuthor Commented:
I have got it a bit closer with this code.

'Set color banding for change in role name
                cellCol1 = 191 'Grey
                cellCol2 = 255 'White
            With myRange2
                .Rows(3).Interior.Color = RGB(cellCol1, cellCol1, cellCol1)
                For lRow = 4 To .Rows.Count Step 1
                        If Not IsError(.Cells(lRow, "A").Value) Then
                            If .Cells(lRow, "A").Value = "" Then
                                .Rows(lRow).Interior.Color = RGB(cellCol1, cellCol1, cellCol1)
                            Else
                                .Rows(lRow).Interior.Color = RGB(cellCol2, cellCol2, cellCol2)
                                cellCol1 = 255
                                cellCol2 = 191
                            End If
                        End If
                Next lRow
            End With

I set row 3 to be grey since this is the first one I want to be grey.
The next row will always be blank so it also needs to be grey, then it finds the next is not blank, therefore must be a new role, sets it to white and then the next is blank so it is also white, but then it starts to alternate setting every role a grey and every blank as white, this is not what i want - see screen shots.

 format resulting from code

 required format example
0
 
shipleypAuthor Commented:
I have kept trying today and probably could of done this better but it work perfectly. I was messing around with if's and select's and ended up with the following code.

            With myRange2
                .Rows(3).Interior.Color = RGB(191, 191, 191)
               
                For lRow = 4 To .Rows.Count Step 1
                        If .Cells(lRow, "A").Value = "" And .Rows(lRow - 1).Interior.ColorIndex <> 15 Then
                                banded = True
                                cellcol1 = 255 ' white
                            Else
                            If .Cells(lRow, "A").Value = "" And .Rows(lRow - 1).Interior.ColorIndex = 15 Then
                                banded = False
                                cellcol1 = 191 ' grey
                                Else
                                If .Cells(lRow, "A").Value <> "" And .Rows(lRow - 1).Interior.ColorIndex = 15 Then
                                    banded = True
                                    cellcol1 = 255 ' white
                                    Else
                                    If .Cells(lRow, "A").Value <> "" And .Rows(lRow - 1).Interior.ColorIndex <> 15 Then
                                        banded = True
                                        cellcol1 = 191 ' grey
                                    End If
                                End If
                            End If
                        End If
                    Select Case banded
                    Case True
                        .Rows(lRow).Interior.Color = RGB(cellcol1, cellcol1, cellcol1)

                    Case False
                        .Rows(lRow).Interior.Color = RGB(cellcol1, cellcol1, cellcol1)
           
                    End Select
                Next lRow
            End With
0
 
shipleypAuthor Commented:
Hi robberbaron,

I substituted your code and it worked well. Actually my code was a bit of a guess went i  added the 3rd and 4th nested if's so thanks for cleaning it up and making it make sense.

Thanks again
Phil.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.