If Cells Contain Text Then Use Formatting

If you have a large amount of data and you want to find text values and distinguish them from the other types of data, you can do it by applying the formatting. It can be text color, background color, etc.

In this lesson, we are going to do it using two different ways.

Use the Go to Special feature

This method is very simple.

First, select the range in which you want to find data.

Navigate to Home >> Editing >> Find & Select >> Go To Special… . Click Constants and uncheck all positions, but Text.

Click OK to apply changes.

Three values are selected. Cell A7 is also selected because 7 is formatted as text, not a number (‘7).

Now, you can format the cells by changing the background color. Go to Home >> Font >> Fill Color.

And this the result.

Format cell with a macro

You can record a macro to see what the code looks like. It’s going to create a lot of unnecessary code. After removing a few lines, we are going to get this simple code.

Code explanation

In the first line of code, it’s going to select the second position under Constants, which is Text. After selecting all text values, it’s going to fill them with yellow, which is represented by the number 65535.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.

Posted in vba