How To Find And Remove Bold Formatting In An Excel Spreadsheet With VBA
January 11, 2018
If your spreadsheet contains unwanted formatting, VBA can selectively remove the formats and add new criteria. A typical scenario might be a conditional format based on specific values but now you need to apply new conditions.
This article explains how you can identify cells with bold type, remove the formatting.and add new criteria.
Identifying Bold Type In Each Cell With VBA
The example we’ll use is a numeric data list that has bold type where the value is over 1000. Now you need to increase the conditional value to 1500, but change the format to bold, and red.
The data might look something like this:
The procedure we’ll write will remove the bold format from each cell before applying the new formatting criteria.
dim rng as range
dim n as long
for x=2 to rng.rows.count
if rng.rows(x).font.bold then
With the bold type removed, the code can now apply the new format criteria.
If myValue > 1500 Then
rng.Rows(x).Font.Bold = True
rng.Rows(x).Font.ColorIndex = 3
The code now produces an output which displays all values greater than 1500 in bold and red type.
This type of procedure can easily be developed to add other formatting types. For example, to increase the font size, the size property can be accessed.
rng.Rows(x).Font.Size = 16
It can be useful to know the different properties available when working with cell formats. One way to do this is by recording a macro. Start recording, and then select a cell value and change a few of the settings such as color and size. If you view the resulting macro you’ll see something like this.
.Name = "Arial"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 3
.Font.Bold = True
The macro displays a number of formatting properties you can then use in your own code and can be added to your code library.
Although Excel has some strong formatting tools, VBA enables developers to customize a solution to suit their own needs. By adding this kind of procedure to a code library it can improve productivity and efficiency to any new project.