How can i skip empty cells with this loop

[mmckay1]

im trying to work on a simple program that it will highlight all cells with a value of 5 or less and VB is not my strong point. The problem is it highlights a million cells in the column because empty is less than 5. this is the code I'm using. I fumbled through forums for a day to get this far. I have a "Next without For" error. Any help will be greatly appreciated thanks.

Sub numfinder()
    Dim cell As Range
    Dim NewRange As Range
    Dim MyCount As Long
    MyCount = 1
    For Each cell In Range("F5:F116")
            If cell.Value = Empty Then Next cell
        If cell.Value <= 5 Then
            If MyCount = 1 Then Set NewRange = cell
            Set NewRange = Application.Union(NewRange, cell)
            MyCount = MyCount + 1
        End If
    Next cell
    NewRange.Interior.ColorIndex = 8
End Sub

 

[Domski]

Like this:

Sub numfinder()
    Dim cell As Range
    Dim NewRange As Range
    Dim MyCount As Long
    MyCount = 1
    For Each cell In Range("F5:F116")
        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 = 8
End Sub

Dom

 

[hiker95]

mmckay1,


Sample raw data before the macro:

Excel Workbook
	F
5	9
6	8
7
8	6
9	5
10	4
11
12	9
13	8
14
15	6
16	5
17	4
18
Sheet1

After the macro:

Excel Workbook
	F
5	9
6	8
7
8	6
9	5
10	4
11
12	9
13	8
14
15	6
16	5
17	4
18
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub numfinderV2()
' hiker95, 03/29/2012
' http://www.mrexcel.com/forum/showthread.php?t=624952
Dim cell As Range
Dim NewRange As Range
Dim MyCount As Long
MyCount = 1
For Each cell In Range("F5:F116")
  If cell = "" Then
    'do nothing
  ElseIf cell.Value <= 5 Then
    If MyCount = 1 Then Set NewRange = cell
    Set NewRange = Application.Union(NewRange, cell)
    MyCount = MyCount + 1
  End If
Next cell
NewRange.Interior.ColorIndex = 8
End Sub

Then run the numfinderV2 macro.

 

[mmckay1]

Re: How can i skip empty cells with this loop *FIXED*

thanks so much domski it works perfectly!