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...
VB Code:
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:
VB Code:
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!![]()




Reply With Quote