I want to leave empty spaces in this spreadsheet for consumable items in an inventory system. It highlights different sections of quantity values with varying minimums. I want to be able to leave up to 10 cells blank in the loop but if i have more than one or two it crashes out. i only started getting this error after adding extra rows to allow for items in the range to be added later. here is the error: runtime error 91 : object variable or with block variable not set
here is the code i am using to select the different values and highlight them
Like i said this WAS totally working untill i added extra rows between ranges. is there a quick fix to this or will i have to re-do everything? if thats the case ill make do without the extra room and teach the next person to use this how to add a range.
thanks in advance
here is the code i am using to select the different values and highlight them
Code:
Sub milling_inserts()
Dim cell As Range
Dim NewRange As Range
Dim MyCount As Long
MyCount = 1
For Each cell In Range("C8:C12")
If cell.Value <> "" Then
If cell.Value <= 10 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("C13:C46")
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("C53:C63")
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("G8:G24")
If cell.Value <> "" Then
If cell.Value <= 10 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("G30:G44")
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("G51:G63")
If cell.Value <> "" Then
If cell.Value <= 10 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("K8:K24")
If cell.Value <> "" Then
If cell.Value <= 10 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("K30:K44")
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("K50:K63")
If cell.Value <> "" Then
If cell.Value <= 10 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
thanks in advance