Excel 2013 VBA: Coloring Visible Rows Only in Used Range When Hide and Unhide Rows

[gertie193]

Hello,

I have code that uses a command button on my spreadsheet to hide rows that contain the text 'Complete' in column AX. When the command button is pressed again, it will unhide the rows. This works. The other code is used to color rows in my data range based on when the value in column J (Hub column) changes and if the Week column (column E) changes within that hub, then it highlights that row, too--alternating between light orange and no color. I've used the code by itself successfully many times in the past, but with no hidden rows.
The problem:
I've combined these 2 codes, and the problem is in the coloring rows code:

1) It doesn't color rows to the end of the used range but stops at the first hidden column, which is column F and mainly is blank. I don't get an error, it just doesn't work correctly. I've used the code by itself successfully in the past, but with no hidden rows, and it colors the used range even if there are hidden blank columns.

2) It colors the hidden rows as well, but I want it to color only the visible rows when the rows are hidden and color all the rows when the rows are unhidden. I figure there is no harm coloring using xlCellTypeVisible even if there are no visible cells. (You can where it didn't color rows properly btw Almo and Alvon in the pic below).

A copy of part of the data is below.
https://drive.google.com/open?id=0B9IyKJSJ52ghdFpodXF5VjFCbmc

What I've tried under Color Rows Code:
1) I've tried adding .Rows.SpecialCells(xlCellTypeVisible) so it reads below but it didn't color the rows correctly.

With ActiveSheet.Rows.SpecialCells(xlCellTypeVisible) 
        r = 4  ' First row of data on row 4 - must arrange table so that data starts on row 4

2) I've also tried adding .SpecialCells(xlCellTypeVisible) after each reference to cells like

If .Cells(r, "E").SpecialCells(xlCellTypeVisible).Value <> .Cells(r - 1, "E").SpecialCells(xlCellTypeVisible).Value Or _, etc.

3) I finally settled on adding .SpecialCells(xlCellTypeVisible) when setting the range for the Color Row code. This where it will at least highlight to the first hidden column but still highlight hidden rows:

Set colorrng = Range("A1:BK" & lastrow).SpecialCells(xlCellTypeVisible)

I would appreciate any help. I just can't get the color visible rows to work with hidden rows or unhidden rows (it does the same re: coloring when unhide the rows).


Full Code:

Private Sub CommandButton1_Click()
'Command Button is to hide rows where column AX contains text, 'Completed' using Command button
'Code then colors visible rows
'Code then calls to another code that colors certain cells


'*******Variables for Hide/Unhide Rows code***********
Dim rng As Range
Dim iRow As Range
Dim wsh As Worksheet
Dim hidden_status As Boolean


'*******Variables for Color Rows code***********
Dim colorrng As Range
Dim r As Long
Dim lastrow As Long
Dim lastcol As Long
Dim colourIt As Boolean
Dim colour As XlColorIndex


'*******Set colourit variable for Color Rows code***********
lastrow = Cells(Rows.Count, "J").End(xlUp).Row
lastcol = Cells(3, Columns.Count).End(xlToLeft).Column
colourIt = False
Set colorrng = Range("A1:BK" & lastrow).SpecialCells(xlCellTypeVisible)


'*******Set rng variable for Hide Rows code***********
Set rng = Range("AX:AX")


'*******HIDE/UNHIDE ROWS CODE***********
If CommandButton1.Caption = "Hide Rows" Then
   CommandButton1.Caption = "Unhide Rows"
Else
   CommandButton1.Caption = "Hide Rows"
End If


For Each iRow In rng.Rows
        If iRow.Value = "Complete" Or iRow.Value = "N/A" Then
           With iRow.EntireRow
                 hidden_status = .Hidden
                .Hidden = Not hidden_status
           End With
        End If
Next iRow


'****[B]PROBLEM IS HERE[/B] [B]- COLOR ROWS CODE[/B]*****
With ActiveSheet
        r = 4  ' First row of data on row 4 - must arrange table so that data starts on row 4


        Do While .Cells(r, "E").Value <> ""
            'See if value has changed - highlight by week within each hub - "E" is Week column, and "G" is Hub column
           
            If .Cells(r, "E").Value <> .Cells(r - 1, "E").Value Or _
               .Cells(r, "J").Value <> .Cells(r - 1, "J").Value Then
                colourIt = Not colourIt
            End If


            'Determine which colour to use on this row - RGB(252, 228, 214) is the lightest orange in the color palette, Orange, Accent, Lighter 80%
            If colourIt Then
                colour = RGB(252, 228, 214)
            Else
                colour = xlColorIndexNone
            End If
            'Apply the colouring - starting in row r = 4, 4th row, and column "A"
           '.Range(.Cells(r, "A"), .Cells(r, .Cells(r, .Columns.Count).End(xlToLeft).Column)).Interior.Color = colour
            colorrng.Rows(r).Interior.Color = colour
                


            'Point to the next row of data
            r = r + 1
        Loop
        
End With
    
'*******CALL TO 'COLOR CELLS WITH 'ADD NODES' TEXT IN THEM' CODE***********
    Call ColorAddToNodes.ColorAddToNodes
    
Application.ScreenUpdating = True


On Error GoTo 0


End Sub

(Credit: hide rows code is originally from Masoud of Stackoverflow 'excel vba toggle hide/unhide range rows across multiple sheets, if cell value 0' and color rows code was originally from YowE3K of StackOverflow 'Shading row to end of values based on change of value in column C then in column B')

 

[tonyyy]

gertie193,

For Problem #1, change this line...

colorrng.Rows(r).Interior.Color = colour

to

colorrng.Rows(r).EntireRow.Interior.Color = colour

For Problem #2, it would be helpful if you post your data rather than just a link to an image.

Cheers,

tonyyy

 

[gertie193]

gertie193,

For Problem #1, change this line...

colorrng.Rows(r).Interior.Color = colour

to

colorrng.Rows(r).EntireRow.Interior.Color = colour

For Problem #2, it would be helpful if you post your data rather than just a link to an image.

Cheers,

tonyyy

Thank you, tonyyy.

I'm not able to post attachments per my permissions below, but I've provided a link where you or anyone can view the file. The code can be viewed by right clicking the red command button that says 'Unhide Rows' while you have it in Design Mode under the Developer tab.

I've set it so the rows are already hidden using the red command button.

https://drive.google.com/open?id=0B9IyKJSJ52ghM3AxcGRpcVFWSzg

Re: Problem#1, I tried adding .Entire Row, but that colors the entire row, and I just want to color the Used Range to column BK.

I fixed it somewhat by using the originally code I had to color the rows and adding .SpecialCells(xlCellTypeVisible). It colors the rows to the end of the Used Range but says "Run-time error '1004' - No cells were found". I tried changing .Columns.Count to "BK" (the last column) but it still gives that error.

.Range(.Cells(r, "A"), .Cells(r, .Cells(r, .Columns.Count).End(xlToLeft).Column)).SpecialCells(xlCellTypeVisible).Interior.Color = colour

Re: Problem#2: You can see in the file that, for ex:, Brisbay and Cinquinto, part 2 in rows 59-80 should be highlighted so that Brisbay is orange but Cinquinto, Part 2 is no color b/c the code says when you change hub names you should change colors. (For clarification, the code also says that if the Week changes from say, W1N1 to W1N2 within a hub, you should change colors.)

I appreciate you or anyone else's help to solve this. Thank you.

 

[tonyyy]

For Problem#1, try surrounding your latest attempt with the following...

'Apply the colouring - starting in row r = 4, 4th row, and column "A"
[COLOR=#ff0000]If Rows(r).Hidden = False Then[/COLOR]
    .Range(.Cells(r, "A"), .Cells(r, .Cells(r, .Columns.Count).End(xlToLeft).Column)).SpecialCells(xlCellTypeVisible).Interior.Color = colour
[COLOR=#ff0000]End If[/COLOR]

 

[tonyyy]

For Problem#2, try adding the lines in red...

With ActiveSheet
'    Columns(6).EntireColumn.Hidden = False
        r = 4  ' First row of data on row 4 - must arrange table so that data starts on row 4
        [COLOR=#ff0000]Range("A4:BK" & lastrow).Interior.Color = xlColorIndexNone[/COLOR]
        
        Do While .Cells(r, "E").Value <> ""
            'See if value has changed - highlight by week within each hub - "E" is Week column, and "J" is Hub column
            'Must arrange data so that "E" is Week column, and "J" is Hub column - can delete extra columns later if had to add to make columns "E" and "F"
            [COLOR=#ff0000]If Rows(r).Hidden = False Then[/COLOR]
                If .Cells(r, "E").Value <> .Cells(r - 1, "E").Value Or _
                   .Cells(r, "J").Value <> .Cells(r - 1, "J").Value Then
                    colourIt = Not colourIt
                End If
           [COLOR=#ff0000] End If[/COLOR]

 

[tonyyy]

And if you want to speed things up a bit, add...

Application.ScreenUpdaing = False

to the top of the code (I put it just above the Dim statements), as well as change the rng reference...

'*******Set rng variable for Hide Rows code***********
'Set rng = Range("AX:AX")
[COLOR=#ff0000]Dim LastRowAX As Long
LastRowAX = Cells(Rows.Count, "AX").End(xlUp).Row
Set rng = Range("AX4:AX" & LastRowAX)[/COLOR]

 

[gertie193]

For Problem#1, try surrounding your latest attempt with the following...

'Apply the colouring - starting in row r = 4, 4th row, and column "A"
[COLOR=#ff0000]If Rows(r).Hidden = False Then[/COLOR]
    .Range(.Cells(r, "A"), .Cells(r, .Cells(r, .Columns.Count).End(xlToLeft).Column)).SpecialCells(xlCellTypeVisible).Interior.Color = colour
[COLOR=#ff0000]End If[/COLOR]

Thank you, tonyyy. This fix for problem #1 worked. It even worked without putting the Special Cells part in the code. I don't know why, but thank you for your help!

 

[gertie193]

And if you want to speed things up a bit, add...

Application.ScreenUpdaing = False

to the top of the code (I put it just above the Dim statements), as well as change the rng reference...

'*******Set rng variable for Hide Rows code***********
'Set rng = Range("AX:AX")
[COLOR=#ff0000]Dim LastRowAX As Long
LastRowAX = Cells(Rows.Count, "AX").End(xlUp).Row
Set rng = Range("AX4:AX" & LastRowAX)[/COLOR]

Thank you! This sped things up fast! Things for helping clean up this code.

 

[tonyyy]

You're very welcome...

 

[gertie193]

For Problem#2, try adding the lines in red...

With ActiveSheet
'    Columns(6).EntireColumn.Hidden = False
        r = 4  ' First row of data on row 4 - must arrange table so that data starts on row 4
        [COLOR=#ff0000]Range("A4:BK" & lastrow).Interior.Color = xlColorIndexNone[/COLOR]
        
        Do While .Cells(r, "E").Value <> ""
            'See if value has changed - highlight by week within each hub - "E" is Week column, and "J" is Hub column
            'Must arrange data so that "E" is Week column, and "J" is Hub column - can delete extra columns later if had to add to make columns "E" and "F"
            [COLOR=#ff0000]If Rows(r).Hidden = False Then[/COLOR]
                If .Cells(r, "E").Value <> .Cells(r - 1, "E").Value Or _
                   .Cells(r, "J").Value <> .Cells(r - 1, "J").Value Then
                    colourIt = Not colourIt
                End If
           [COLOR=#ff0000] End If[/COLOR]

I just tested this code, as well. Thank you, tonyyy. It works.

I just noticed that the code doesn't unhide the last row for anyone that tries to use this code. The last row is one of the rows that gets hidden when code runs to hide rows, but I will look into fixing that. You have solved my problem for coloring the rows. Thank you so much!