Looping through all WS in WB not working using For

[Prevost]

Hi There. My code below is supposed to highlight all the rows of the cells in a specified range (in column B) that begin with a letter. I am trying to loop through all the worksheets in the workbook and am using the FOR counter from 2 to the end of the WS. However, it only works on the active sheet. Does anyone know why? I also cannot get it to work by using 'For Each WS in ActiveWorkbook.Worksheets', even after declaring WS as Worksheet. Any help would be greatly appreciated!

Sub Highlight()
    Dim WS As Worksheet
    Dim Cell As Range, NewRange As Range
    Dim i As Long, j As Long
        For j = 2 To Worksheets.Count
            With Sheets(j)
                i = Cells(Rows.Count, "B").End(xlUp).Row
                Set NewRange = Range(Cells(2, 2), Cells(i, 2))
            End With
                For Each Cell In NewRange
                    If IsNumeric(Left(Cell, 1)) = False Then
                        Cell.EntireRow.Interior.ColorIndex = 8
                    End If
                Next Cell
        Next j
End Sub

 

[JoeMo]

Hi There. My code below is supposed to highlight all the rows of the cells in a specified range (in column B) that begin with a letter. I am trying to loop through all the worksheets in the workbook and am using the FOR counter from 2 to the end of the WS. However, it only works on the active sheet. Does anyone know why? I also cannot get it to work by using 'For Each WS in ActiveWorkbook.Worksheets', even after declaring WS as Worksheet. Any help would be greatly appreciated!

Sub Highlight()
    Dim WS As Worksheet
    Dim Cell As Range, NewRange As Range
    Dim i As Long, j As Long
        For j = 2 To Worksheets.Count
            With Sheets(j)
                i = Cells(Rows.Count, "B").End(xlUp).Row
                Set NewRange = Range(Cells(2, 2), Cells(i, 2))
            End With
                For Each Cell In NewRange
                    If IsNumeric(Left(Cell, 1)) = False Then
                        Cell.EntireRow.Interior.ColorIndex = 8
                    End If
                Next Cell
        Next j
End Sub

Try qualifying the ranges in your With-End With construct like this:

With Sheets(j)
    i = .Cells(Rows.Count, "B").End(xlUp).Row
    Set NewRange = .Range(.Cells(2, 2), .Cells(i, 2))
End With