Hi All
I have created a macro below, everything works fine except the filters. I don't want filters to be shown on the tab 'configuration' and 'project managers', however the filters are shown on the tabs. Please could anyone spot any errors on my macro, many thanks.
I have created a macro below, everything works fine except the filters. I don't want filters to be shown on the tab 'configuration' and 'project managers', however the filters are shown on the tabs. Please could anyone spot any errors on my macro, many thanks.
VBA Code:
Private Sub MI()
Dim sh As Worksheet
Sheets("All faculties results").Delete
Application.ScreenUpdating = False
For Each sh In Worksheets
If sh.Name <> "configuration" Or sh.Name <> "project managers" Then
sh.Activate
' paste in formula in column M and N
lastRow = Range("A" & Rows.Count).End(xlUp).Row
Columns("L").Insert
Range("L12") = "Late/Future"
Range("L13").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-2]>=R4C2,""Future"",""Late"")"
If lastRow > 13 Then
Range("L13").AutoFill Destination:=Range("L13:L" & lastRow)
End If
'Paste in table text
Range("G3") = "W20 Late"
Range("G4") = "Future"
Range("G3").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = RGB(205, 255, 225)
End With
Range("G4").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = RGB(255, 204, 255)
End With
'hightlight Rows
For i = 2 To lastRow
If Cells(i, 8).Value = "B20" And Cells(i, 12).Value = "Late" Then
Rng = "A" & i & ":" & "L" & i
Range(Rng).Interior.Color = RGB(255, 255, 204)
ElseIf Cells(i, 8).Value = "P20" And Cells(i, 12).Value = "Late" Then
Rng = "A" & i & ":" & "L" & i
Range(Rng).Interior.Color = RGB(204, 236, 255)
ElseIf Cells(i, 8).Value = "W20" And Cells(i, 12).Value = "Late" Then
Rng = "A" & i & ":" & "L" & i
Range(Rng).Interior.Color = RGB(205, 255, 225)
ElseIf Cells(i, 12).Value = "Future" Then
Cells(i, 12).Interior.Color = RGB(255, 204, 255)
End If
Next i
Range("A12:N12").Select
Selection.AutoFilter
'filter to show blank Actual dates
ActiveSheet.Range("A12:M" & i).AutoFilter Field:=11, Criteria1:="="
Range("A1").Value = "'W20 Late & Future items' or 'Items due to handover' by CAU"
Columns.AutoFit
Columns("A:B").ColumnWidth = 20
End If
Next sh
Application.ScreenUpdating = True
End Sub