code error help runtime 91

[mmckay1]

I have been using this code as a macro to quickly highlight all items within a range under a certain value. i have just cut and pasted the code across several sheets and just changed the range of cells where it looks.
cut and paste and change the range. over and over and over again.
now im getting a runtime error 91bject variable or with block not set. i;m by no means good at VBA this is just a small project im working on at school and i cant debug this. any help would be appreciated.
heres my code that works on all the other worksheets

Sub Insert_Inventory()
    Dim cell As Range
    Dim NewRange As Range
    Dim MyCount As Long
    MyCount = 1
    For Each cell In Range("E8:E13") ' These values indicate the range in the "F" column that the product numbers run from
            If cell.Value <> "" Then  'If you add or take away a row in the range simply change the numbers in the range ie. "F5:F14"
            If cell.Value <= 5 Then
                If MyCount = 1 Then Set NewRange = cell
                Set NewRange = Application.Union(NewRange, cell)
                MyCount = MyCount + 1
            End If
        End If
    Next cell
    NewRange.Interior.ColorIndex = 3 ' <--- this number dictates the colour the cell will turn if it needs to be reordered
        For Each cell In Range("E22:E26")
        If cell.Value <> "" Then
            If cell.Value <= 15 Then
                If MyCount = 1 Then Set NewRange = cell
                Set NewRange = Application.Union(NewRange, cell)
                MyCount = MyCount + 1
            End If
        End If
    Next cell
    NewRange.Interior.ColorIndex = 3
    For Each cell In Range("G36:G42")
        If cell.Value <> "" Then
            If cell.Value <= 5 Then
                If MyCount = 1 Then Set NewRange = cell
                Set NewRange = Application.Union(NewRange, cell)
                MyCount = MyCount + 1
            End If
        End If
    Next cell
    NewRange.Interior.ColorIndex = 3
    For Each cell In Range("G48:G65")
        If cell.Value <> "" Then
            If cell.Value <= 5 Then
                If MyCount = 1 Then Set NewRange = cell
                Set NewRange = Application.Union(NewRange, cell)
                MyCount = MyCount + 1
            End If
        End If
    Next cell
    NewRange.Interior.ColorIndex = 3
End Sub

 

[Jonmo1]

Welcome to the board..

My guess would be that it didn't find any cells in the given range that met the criteria of <> "" and <=5

Try

If Not NewRange Is Nothing Then
    NewRange.Interior.ColorIndex = 3
End If

 

[mmckay1]

it is already coded to skip blank cells. and go to the next range. i tried stepping through the code with F8 it goes through the first range then i get the error when it goes to the next one. i just filled in my cells with dummy numbers and now it seems to be running fine. i think i just need to get a coffee. too early for vba thanks for the help though

 

[Jonmo1]

it is already coded to skip blank cells. and go to the next range.

I know, that's my point...

If ALL the cells in the first range E8:E13 are blank or NOT <=5
Then the 2 IF's skips them all
If cell.Value <> "" Then
If cell.Value <= 5 Then


And if they are all skipped, then this line never executes
If MyCount = 1 Then Set NewRange = cell

so NewRange has NOT been set if all the cells are blank or NOT <=5

Causing the error when it gets to the line trying to set the color of NewRange.
NewRange.Interior.ColorIndex = 3

 

[Jonmo1]

Also, you should be setting MyCount back to 1 between each loop.

Otherwise this line never executes in the 2nd and subsequent loops
If MyCount = 1 Then Set NewRange = cell

Then, NewRange will still include all the cells that met the criteria in the previous loop.

 

[mmckay1]

yea i put some numbers in it works now . there will always be something in the cells when im done with it i think i had this issue before. your code also works thanks