Excel macro problem (Simple?)

[danielzzz]

I'm a newbie to macro editing (though I do know basic VB) and am trying to create a macro which takes each cell in row B in a sheet from row 26 to 11230 and test if there are less than two spaces in the text of that cell (less than three words). If there's only one or zero spaces, the cell should be made invisible by making the text color white. Also, if the cell contains either "/" or "," or "&" somewhere in the text, the cell should NOT be whited out, even though there's less than three words. Finally, some cells may contain a space in the end of the text, why that last character is best not checked at all (the important thing is that no unsure cells are whited out). The main problem is how I convert numbers and strings into the irritating type Range, which is how I try to check all cells from X to Y. Here's my failing code, beware of stupid variable names...

Sub Makro4()
'
' Makro4 Makro
' Makrot inspelat 2005-12-30 av Joel
'
 
'
    addd = 26
    byp = "B"
    While (addd < 11230)
    
    gtt = byp + addd
    
    sirr = Range(gtt).Value
    numm = 1
    countt = 0
    While (numm < Len(sirr))
        charr = Mid(sirr, numm, 1)
        If (charr = " ") Then
            countt = countt + 1
        End If
        If (charr = "/" Or charr = "&" Or charr = ",") Then
            numm = 99
        End If
        numm = numm + 1
    Wend
    If (countt < 2 And Not (numm = 100)) Then
        Range("B" + addd).Select
        Selection.Font.ColorIndex = 2
    End If
    addd = addd + 1
    
    Wend
End Sub

Variable declarations:

Dim gtt As Range
Dim addd As Integer
Dim sirr As String
Dim charr As String
Dim numm As Integer
Dim countt As Integer
Dim byp As Range

Thanks for your help!

[Comintern]

Try this:

Public Sub CheckCells()
 
    Dim oSheet As Excel.Worksheet, lRow As Long, sTest As String, sWords() As String, bSkip As Boolean
    
    Set oSheet = Application.ActiveSheet
    
    For lRow = 26 To 11230              'loop through the cells
        sTest = oSheet.Cells(lRow, 2).Value
        If InStr(1, sTest, "/") Then bSkip = True
        If InStr(1, sTest, "&") Then bSkip = True
        If InStr(1, sTest, ",") Then bSkip = True
        If Not bSkip Then
            sWords = Split(sTest, " ")
            If UBound(sWords) > 2 Then
                oSheet.Cells(lRow, 2).Font.ColorIndex = 2
            End If
        End If
        bSkip = False
    Next lRow
    
    Set oSheet = Nothing
    
End Sub

[danielzzz]

Thanks! Though the macro was supposed to white out cells with less than two spaces I easily fixed it so it works perfectly. I shall learn more about macro writing when I have time. Thanks again !