macro that formats visible rows after a list is sorted

[david graham]

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.

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

 

[david graham]

This is a better example of my code, thanks

'FORMAT 'FORMAT LABOR ITEMS
  Sheets("LIST").Select
 Range("R1").Select
    Selection.AutoFilter Field:=1, Criteria1:="6"
 
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

 

[crimson_b1ade]

Duplicate post. Active Thread here: http://www.mrexcel.com/forum/showthread.php?t=377510