Macro acting on all Sheets
 

I have a WB with 19 WS in it. I have a set of Macros (3 so far) which are
intended to work on each WS individually. They starts off similarly to this:

myCount = 0 'Counts the Rows in the inner loops
NewStartRow = 2 'Start Row of each set
myRowCount = 0 'Counts the Rows in the outer loop

With ActiveSheet
Set rngLastRow = .Cells(.UsedRange.Rows.Count,
..UsedRange.Columns.Count).EntireRow
End With

Do While NewStartRow <= rngLastRow.Row 'Check every row
If myRowCount rngLastRow.Row Then
Exit Do
End If

Set myCell = Cells(NewStartRow + myRowCount, 3) 'First time starts
in Row 2

When I run them I discover that they are each running on all 19 pages at the
same time. I just thought, there is some other code in the Macro that might
be the guilty party. It is this:

If mySubTotal < CurVal Then 'Highlight set where there is a
mis-match
Selection.End(xlToLeft).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(("A" & NewStartRow - varOffset), ("S" &
NewStartRow - 1)).Select
With Selection.Interior
.ColorIndex = 40
.Pattern = xlSolid
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
End If

Is the problem in that "Range(Selection...)" stuff?
--
Dave
Temping with Staffmark
in Rock Hill, SC

Macro acting on all Sheets
 

Dave, I don't see anything in the posted code that would affect
more than the active sheet. You said there were three macros.
If you have one that has a section with something like:

For Each Sheet in ActiveWorkbook.Worksheets...

Then it could be calling the macro you posted and would run it
for each of the 19 worksheets. As for the selection stuff, see
below comments.

If mySubTotal < CurVal Then 'Highlight set where there is a
mis-match
Selection.End(xlToLeft).Select
Range(Selection, Selection.End(xlToRight)).Select

'The two rows above can be eliminated.
'They do nothing more that move the cursor from right to left
and back.

'The code below adds a color to a range interior and puts a
'thick border at the bottom of the cell

Range(("A" & NewStartRow - varOffset), ("S" &
NewStartRow - 1)).Select
With Selection.Interior
.ColorIndex = 40
.Pattern = xlSolid
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
End If


"Dave Birley" wrote:

I have a WB with 19 WS in it. I have a set of Macros (3 so far) which are
intended to work on each WS individually. They starts off similarly to this:

myCount = 0 'Counts the Rows in the inner loops
NewStartRow = 2 'Start Row of each set
myRowCount = 0 'Counts the Rows in the outer loop

With ActiveSheet
Set rngLastRow = .Cells(.UsedRange.Rows.Count,
.UsedRange.Columns.Count).EntireRow
End With

Do While NewStartRow <= rngLastRow.Row 'Check every row
If myRowCount rngLastRow.Row Then
Exit Do
End If

Set myCell = Cells(NewStartRow + myRowCount, 3) 'First time starts
in Row 2

When I run them I discover that they are each running on all 19 pages at the
same time. I just thought, there is some other code in the Macro that might
be the guilty party. It is this:

If mySubTotal < CurVal Then 'Highlight set where there is a
mis-match
Selection.End(xlToLeft).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(("A" & NewStartRow - varOffset), ("S" &
NewStartRow - 1)).Select
With Selection.Interior
.ColorIndex = 40
.Pattern = xlSolid
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
End If

Is the problem in that "Range(Selection...)" stuff?
--
Dave
Temping with Staffmark
in Rock Hill, SC

Macro acting on all Sheets
 

Thanks for clarifying (and also for helping me to drop two un-needed rows of
code).

Programming, I have learned over the years, is a lot like genealogy.
Sometimes the negative information is as useful as the positive. It
eliminates unwarranted suspicions <g!
--
Dave
Temping with Staffmark
in Rock Hill, SC


"JLGWhiz" wrote:

Dave, I don't see anything in the posted code that would affect
more than the active sheet. You said there were three macros.
If you have one that has a section with something like:

For Each Sheet in ActiveWorkbook.Worksheets...

Then it could be calling the macro you posted and would run it
for each of the 19 worksheets. As for the selection stuff, see
below comments.

If mySubTotal < CurVal Then 'Highlight set where there is a
mis-match
Selection.End(xlToLeft).Select
Range(Selection, Selection.End(xlToRight)).Select

'The two rows above can be eliminated.
'They do nothing more that move the cursor from right to left
and back.

'The code below adds a color to a range interior and puts a
'thick border at the bottom of the cell

Range(("A" & NewStartRow - varOffset), ("S" &
NewStartRow - 1)).Select
With Selection.Interior
.ColorIndex = 40
.Pattern = xlSolid
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
End If


"Dave Birley" wrote:

I have a WB with 19 WS in it. I have a set of Macros (3 so far) which are
intended to work on each WS individually. They starts off similarly to this:

myCount = 0 'Counts the Rows in the inner loops
NewStartRow = 2 'Start Row of each set
myRowCount = 0 'Counts the Rows in the outer loop

With ActiveSheet
Set rngLastRow = .Cells(.UsedRange.Rows.Count,
.UsedRange.Columns.Count).EntireRow
End With

Do While NewStartRow <= rngLastRow.Row 'Check every row
If myRowCount rngLastRow.Row Then
Exit Do
End If

Set myCell = Cells(NewStartRow + myRowCount, 3) 'First time starts
in Row 2

When I run them I discover that they are each running on all 19 pages at the
same time. I just thought, there is some other code in the Macro that might
be the guilty party. It is this:

If mySubTotal < CurVal Then 'Highlight set where there is a
mis-match
Selection.End(xlToLeft).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(("A" & NewStartRow - varOffset), ("S" &
NewStartRow - 1)).Select
With Selection.Interior
.ColorIndex = 40
.Pattern = xlSolid
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
End If

Is the problem in that "Range(Selection...)" stuff?
--
Dave
Temping with Staffmark
in Rock Hill, SC