Highlighting leading or trailing spaces


J

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
 
Ad

Advertisements

R

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
 
Ad

Advertisements

D

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top