View Single Post
  #10   Report Post  
Posted to microsoft.public.excel.programming
Claus Busch Claus Busch is offline
external usenet poster
 
Posts: 3,872
Default Formatting multiple sheets within workbook.

Hi,

Am Thu, 30 Mar 2017 03:25:41 -0700 (PDT) schrieb Living the Dream:

Thank you once again Garry.

I decided to go back to the drawing board and the melting-pot and cam up with the following which works to a degree as it gives me the end result I'm looking for but with 2 issues.

1. It keeps flickering & looping over the same range on the same page.
2. It does not cycle through all the other sheet, just the active one.


the monitor flickering is because of Selection.

Try:

For Each sh In ActiveWorkbook.Sheets
If InStr(sExclShts, sh.Name) Then

With sh
.Range(.Cells(11, 1), .Cells(26, 14)).Sort key1:=.Cells(4, 10), order1:=xlAscending, Header:=xlYes

For col = 1 To 13
Select Case .Cells(10, col).Value
Case "Route Ref"
myRef = col
End Select
Next col
For i = 11 To 26
myRef = .Cells(i, "A").Value
If myRef < .Cells(i + 1, "A").Value Then
With .Range(.Cells(i, 1), .Cells(i, 14))
With .Borders(xlEdgeBottom)
.LineStyle = xlDouble
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThick
End With
End With
End If
For Each c In rng
If c = "" Then
With c
.EntireRow.Delete
End With
End If
Next c
Next i
End With
End If
Next sh


Regards
Claus B.
--
Windows10
Office 2016