[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Change row color each time a value changes

Posted on 2011-10-27
8
Medium Priority
?
619 Views
Last Modified: 2013-11-27
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
0
Comment
Question by:shipleyp
  • 4
  • 2
7 Comments
 
LVL 17

Expert Comment

by:aflockhart
ID: 37045247
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
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 37048625
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
 

Author Comment

by:shipleyp
ID: 37054289
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:shipleyp
ID: 37054447
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
 

Author Comment

by:shipleyp
ID: 37055250
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
 
LVL 32

Accepted Solution

by:
Robberbaron (robr) earned 1500 total points
ID: 37055595
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
 

Author Closing Comment

by:shipleyp
ID: 37059973
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

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question