Highlighting leading or trailing spaces

[JackGombola]

I have church members who submit Excel 2003 spreadsheets to me with name and
address information in columns A thru E (Name, address, city, state, zip).
Some sheets are submitted with either leading or trailing spaces in various
cells.

I would like a macro that would inspect each cell in every column for
leading or trailing spaces and highlight them with a blue background color so
that the users can see the spaces when I return the sheets to them.

Thanks,
Jack

 

[Rick Rothstein]

What about a macro that simply removes any leading or trailing spaces
instead...

Sub RemoveLeadingTrailingBlanks()
Dim X As Long, Z As Long, LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For X = 1 To LastRow
For Z = 1 To 5
Cells(X, Z).Value = Trim(Cells(X, Z).Value)
Next
Next
End Sub

 

[Dave Peterson]

I think it's a better idea to fix the problem, too. But if you want...

If you're not using conditional formatting, you could use that.

Select your range to format (say A1:x9999)
With A1 the activecell
format|conditional formatting
formula is:
=(COUNTIF(A1," *")+COUNTIF(A1,"* "))>0
and give it a nice pattern.