Pinterest Pixel

Highlight Cells with a Single Space Using Macros In Excel

Bryan
If you had a spreadsheet with a lot of single spaces, it is very annoying because it is hard to see.
You can use Excel Macros to highlight cells with a single space easily!
Highlight Cells with a Single Space Using Macros In Excel | MyExcelOnline Highlight Cells with a Single Space Using Macros In Excel | MyExcelOnline

Make sure your Excel has the Developer Tab enabled following this tutorial.

I explain how you can do this below step by step!

What does it do?

Highlights all cells with a single space

Copy Source Code:

</p>
<p>Sub HighlightTheSingleSpaces()</p>
<p>Dim range As Range</p>
<p>For Each range In ActiveSheet.UsedRange<br />
'Check if it's a single space, then change the style to be Note<br />
If range.Value = &amp;quot; &amp;quot; Then<br />
range.Style = &amp;quot;Note&amp;quot;<br />
End If<br />
Next range</p>
<p>End Sub</p>
<p>

Final Result: 

Highlight Cells with a Single Space Using Macros In Excel

Exercise Workbook:

Highlight Cells with a Single Space Using Macros In Excel | MyExcelOnline

Download excel workbookHighlight-Cells-with-a-Single-Space.xlsm


This is our initial set of data, the ones enclosed in red are the ones with a single space:

Highlight Cells with a Single Space Using Macros In Excel

STEP 1: Go to Developer > Code > Visual Basic

Highlight Cells with a Single Space Using Macros In Excel | MyExcelOnline

 

STEP 2: Paste in your code and Select Save. Close the window afterwards.

Highlight Cells with a Single Space Using Macros In Excel

 

STEP 3: Let us test it out!

Open the sheet containing the data. Go to Developer > Code > Macros

Highlight Cells with a Single Space Using Macros In Excel | MyExcelOnline

 

Make sure your macro is selected. Click Run.

Highlight Cells with a Single Space Using Macros In Excel

 

With just one click, all of the cells with a single space are now highlighted!

 Highlight Cells with a Single Space Using Macros In Excel

 

How to Highlight Cells with a Single Space Using Macros In Excel

 

101 Macros Book

If you like this Excel tip, please share it
Highlight Cells with a Single Space Using Macros In Excel | MyExcelOnline
Bryan

Bryan Hong is an IT Software Developer for more than 10 years and has the following certifications: Microsoft Certified Professional Developer (MCPD): Web Developer, Microsoft Certified Technology Specialist (MCTS): Windows Applications, Microsoft Certified Systems Engineer (MCSE) and Microsoft Certified Systems Administrator (MCSA).

He is also an Amazon #1 bestselling author of 4 Microsoft Excel books and a teacher of Microsoft Excel & Office at the MyExecelOnline Academy Online Course.

See also  Highlight Alternate Rows in Selection Using Macros In Excel

Star 30 Days - Full Access Star

One Dollar Trial

$1 Trial for 30 days!

Access for $1

Cancel Anytime

One Dollar Trial
  • Get FULL ACCESS to all our Excel & Office courses, bonuses, and support for just USD $1 today! Enjoy 30 days of learning and expert help.
  • You can CANCEL ANYTIME — no strings attached! Even if it’s on day 29, you won’t be charged again.
  • You'll get to keep all our downloadable Excel E-Books, Workbooks, Templates, and Cheat Sheets - yours to enjoy FOREVER!
  • Practice Workbooks
  • Certificates of Completion
  • 5 Amazing Bonuses
Satisfaction Guaranteed
Accepted paymend methods
Secure checkout

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...