I'm really new to macros and need help desugging something.
I recorded a macro that (using the example below) when I click on a button it hides all columns in my sheet except the month of February. Then (this is the bit I'm struggling with) I need to hide all the rows with cells with no fill colour in February's range of cells (here it's H5:K212).
I don't understand the error really so any help would be appreciated!
I need to do this with all the months of the year so if I can understand my example month (Feb) then I can adapt the range of cells to suit the others.
Sub Feb_button()
'
' Feb_button Macro
'
'
Columns("D:G").Select
Selection.EntireColumn.Hidden = True
Columns("L:BD").Select
Selection.EntireColumn.Hidden = True
Range("H4:K212").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Range("BF8").Select
Dim ws As Worksheet: Set ws = Sheets("PA Yearly Servicing Schedule")
Dim LR As Long, LC As Long, lcell As Long
Dim myRange As Range, rCell As Range
Dim bHide As Boolean
Application.ScreenUpdating = False
LC = ws.Cells(5, Columns.Count).End(xlToLeft).Column
LR = ws.Range("D" & Rows.Count).End(xlUp).Row
For lcell = 6 To LR
bHide = True
Set myRange = ActiveSheet.Range("H5:K212").Select
For Each rCell In myRange
If rCell.Interior.ColorIndex <> -4142 Then 'has color
bHide = False
Exit For
End If
Next rCell
If bHide = True Then
ws.Range("A" & lcell).EntireRow.Hidden = True
End If
Next lcell
Application.ScreenUpdating = True
End Sub
Bookmarks