My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

Highlight Text, Numbers, Dates and Formulas on a Sheet

You are here: Home / Excel VBA / Highlight Text, Numbers, Dates and Formulas on a Sheet
show text, numbers, dates, formulas on sheet
by

Imagine that you are preparing data for a pivot table, and you want to make sure that all the cells contain the correct data type.

We know that a date or number entered as text can mess things up so if we had a quick way to identify these problems, it could save a lot of time and effort.

This VBA routine allows us to quickly identify such issues.

Source Data

We have data laid out like this, and everything looks ok.

source data

But, some of those dates are actually text, and so are some of the numbers.

The VBA color codes each of the data types differently so you can easily see where the problems are.

  • Numbers are light blue
  • Formulae are yellow
  • Text is green
  • Dates are mid-blue

source data showing problems

How the Code Works

The VBA uses the SpecialCells method to create a range object for each of the groups of cells containing formulae, text and numbers.

As dates are numbers in Excel, the numbers range contains both what we would consider numbers, and dates.

By using the Areas property of each of these ranges, we can set the background color, which in VBA is the cell's Interior.ColorIndex, to a color of our choosing.

I've created a form to allow the user to check which type of data they want highlighted. If a check box is checked, that data type is highlighted. If the check box is not checked, then cells containing that corresponding data type has no background color. I've included a Reset button to allow users to quickly clear all check boxes and cell colors.

userform to select what data to highlilght

For this demonstration I've run the macro from a shape on the sheet, but it would be more useful to store it in your PERSONAL.XLSB and then run it from the Ribbon or QAT.

It's always a good idea to create a copy of your data before working on it like this, if for nothing other than you will lose any colors you have applied to cells.

Choosing Colors for Highlighting

Colors are specified by numeric values, and I used David McRitchie's post on the Excel color palette to choose mine.

I've hard coded the values into four variables, and tried to choose ones that won't burn your eyes.

color values

You can of course choose your own colors.

A nice enhancement would be to use a color picker on the form, but I think this is another blog topic.

Acknowledgement

I found code by John Walkenbach to create a worksheet map and what I have written here is based on that.

Sample Code

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
show text, numbers, dates, formulas on sheet

Microsoft Power BI Community Super User Logo

AUTHOR Philip Treacy Co-Founder / Owner at My Online Training Hub

Systems Engineer with 30+ years working for companies like Credit Suisse and E.D.S. in roles as varied as Network & Server Support, Team Leader and Consultant Project Manager.

These days Philip does a lot of programming in VBA for Excel, as well as PHP, JavaScript and HTML/CSS for web development.

He's particularly keen on Power Query where he writes a lot of M code.

When not writing blog posts or programming for My Online Training Hub, Philip can be found answering questions on the Microsoft Power BI Community forums where he is a Super User.

More Excel VBA Posts

macro to center across selection
Use this Excel Center Across Selection shortcut macro generated by ChatGPT instead of merge and center.
Display All Matches from Search in Userform ListBox
Search a range for all partial and full matches of a string, and display matching records (entire rows) in a userform listbox. Sample code and userform.
animating excel charts
Use animation correctly to enhance the story your data is telling. Don't animate your chart just for some eye candy. Sample code and workbook to download.
dynamic data validation lists in userforms
Data validation lists using the same source that are dynamically modified to prevent the same choice being made in each list.
show report filter pages for power pivot pivottables
PivotTables created from Power Pivot can't use the 'Show Report Filter Pages' option. But this piece of VBA allows you to do just that.
charting real time data in excel
Receive data in real time and chart the data as it arrives. Can be used to chart things like stock prices or sensor readings. Sample code and workbook
select multiple items from drop down data validation list
Choose multiple items from a data validation (drop down) list and store them all in the same cell. Sample workbook with working VBA.
Excel Calendar (Date Picker) to Use in Worksheets and Userforms
Easy to use, highly customizable and multi-language. This date picker is implemented as a userform that is simple to integrate into your workbook.
automating and emailing pivot table reports
Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
search for data with userform
Search a list of records (like a table) using a user form, and then populate the fields of the search form when the record is found.


Category:

Reader Interactions

Comments

  1. Paul S.

    Wonderful code! Great tool for troubleshooting! From my testing, if all four items are checked in the form, and if the worksheet does not have one of the types (such as formula), then the routine errors out. This is not a major issue, since the macro can then be rerun without the formula box being checked – but it would be nice if the macro worked fully the first time.

    Thanks again Phil for your great work!

    Reply

    • Philip Treacy

      Thanks Paul,

      You shouldn’t get that error as I fixed the code to prevent this happening. You must have an older copy of the workbook. Please download it again and let me know if you are still getting those errors.

      Regards

      Phil

      Reply

  2. jim again

    one can add select constants / formulae (and comments / data validation / conditional formatting) to the QAT by going to Editing/Find&Select on the Home ribbon and right clicking the desired option

    nothing to undo and no VBA (and no date selection nor automatic shading, but shading’s only another click away and constants that are dates can be selected by using ctrl-F, “/” , Find All, ctrl-A)

    not nearly as elegant as Simon but still effective

    Reply

    • Philip Treacy

      Hi Jim,

      Thanks for pointing out an alternative. I personally don’t like that I have to de-select the found cells every time before selecting the next lot though!

      There’s no distinction between text, dates and numbers either but I guess it’s horses for courses and whatever works best for you.

      Regards

      Phil

      Reply

  3. Col Delane

    Another useful tool, Phil. I’ve previously used a similar approach but the result was achieved via Conditional Formatting functionality – your macro is much easier.

    I gather that the target range includes ALL used cells on the active sheet. If so, how can your code be adapted to target only a user selected range – by changing “ActiveSheet” in each iteration of “With ActiveSheet.Range(Area.Address)” to “Selection”?

    Reply

    • Philip Treacy

      Thanks Col.

      Yes the original macro will include all cells on the sheet. To just work with a selection of cells you need to change 3 lines of code.

      At the top of the ShowDataTypes sub, you’ll see these lines where the various ranges that the routine uses are set

      Set FormulaCells = Range("A1").SpecialCells(xlFormulas, xlNumbers + xlTextValues + xlLogical)
Set TextCells = Range("A1").SpecialCells(xlConstants, xlTextValues)
Set NumericCells = Range("A1").SpecialCells(xlConstants, xlNumbers)

      change these to

      Set FormulaCells = Selection.SpecialCells(xlFormulas, xlNumbers + xlTextValues + xlLogical)
Set TextCells = Selection.SpecialCells(xlConstants, xlTextValues)
Set NumericCells = Selection.SpecialCells(xlConstants, xlNumbers)

      So we are just changing Range(“A1”) to Selection

      Cheers

      Phil

      Reply

  4. Frank

    I have downloaded the sample workbook, stored it but the Show Data Types button doesn’t run the macro (I have enabled macros in the Trust Center). What is my problem?

    Reply

    • Philip Treacy

      Hi Frank,

      When you say you have enabled macros in the Trust Center, do you mean you have it set to the ‘Enable all macros (not recommended; potentially dangerous code can run)’ setting? That’s the only setting in the TC that allows macros to run.

      In the Trust Center a better setting is ‘Disable all macros with notification’. When this workbook is then opened, you should be prompted to enable the macros in it.

      Phil

      Reply

  5. jim

    Great idea, nicely packaged

    I use a similar technique to highlight which numbers have been updated from a default value
    Defaults are formulae that can be overwritten with a number

    I have a defined name IsFormula = GET.CELL(48,INDIRECT(“rc”,)) and then use that with conditional formatting to make bold any of the target cells that are no longer a formula

    It’s not totally foolproof but it does what I need it to (NB needs saving as xlsm)

    I think I might have done this with a little help from JW too

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Current ye@r *