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.
2) I've also tried adding .SpecialCells(xlCellTypeVisible) after each reference to cells like
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:
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:
(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')
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.
Code:
With ActiveSheet.Rows.SpecialCells(xlCellTypeVisible)
r = 4 ' First row of data on row 4 - must arrange table so that data starts on row 42) I've also tried adding .SpecialCells(xlCellTypeVisible) after each reference to cells like
Code:
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:
Code:
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:
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')
