david graham
Active Member
- Joined
- Dec 6, 2007
- Messages
- 345
I use the code below to sort a list, & then format only the visible rows. I need an addition to this code that will keep it from trying to format if there are no visible cells in the selection.
Code:
Range("R1").Select
Selection.AutoFilter Field:=1, Criteria1:="3"
Set r = ThisWorkbook.Worksheets("LIST").Range("B12:b30000").SpecialCells(xlCellTypeVisible)
With r.Interior
.ColorIndex = 6
.Pattern = xlSolid
r.Font.ColorIndex = 6
r.HorizontalAlignment = xlCenter
End With
Set r = ThisWorkbook.Worksheets("LIST").Range("c12:c30000").SpecialCells(xlCellTypeVisible)
With r.Interior
.ColorIndex = 6
.Pattern = xlSolid
r.Font.ColorIndex = 1
r.Font.Bold = True
r.HorizontalAlignment = xlLeft
r.Borders(xlDiagonalDown).LineStyle = xlNone
r.Borders(xlDiagonalUp).LineStyle = xlNone
r.Borders(xlEdgeLeft).LineStyle = xlNone
End With
With r.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 1
End With
With r.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 1
End With
With r.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 1
End With
Set r = ThisWorkbook.Worksheets("LIST").Range("d12:H30000").SpecialCells(xlCellTypeVisible)
With r.Interior
.ColorIndex = 6
.Pattern = xlSolid
r.Font.ColorIndex = 1
r.HorizontalAlignment = xlCenter
End With
'Selection.AutoFilter Field:=1
'FORMAT LABOR ITEMS
Sheets("LIST").Select
Range("R1").Select
Selection.AutoFilter Field:=1, Criteria1:="6"
Range("b1").Select
Set r = ThisWorkbook.Worksheets("LIST").Range("B14:b30000").SpecialCells(xlCellTypeVisible)
With r.Interior
.ColorIndex = 2
.Pattern = xlSolid
r.HorizontalAlignment = xlLeft
r.VerticalAlignment = xlTop
r.WrapText = True
r.Font.ColorIndex = 2
r.RowHeight = 30.75
End With
Set r = ThisWorkbook.Worksheets("LIST").Range("C14:C30000").SpecialCells(xlCellTypeVisible)
With r.Interior
.ColorIndex = 2
.Pattern = xlSolid
r.HorizontalAlignment = xlLeft
r.VerticalAlignment = xlTop
r.WrapText = True
r.Font.ColorIndex = 1
r.RowHeight = 30.75
End With
Set r = ThisWorkbook.Worksheets("LIST").Range("D14:E30000").SpecialCells(xlCellTypeVisible)
With r.Interior
.ColorIndex = 15
.Pattern = xlSolid
r.HorizontalAlignment = xlCenter
r.VerticalAlignment = xlCenter
r.WrapText = True
r.Font.ColorIndex = 1
r.RowHeight = 30.75
End With