Contact Us

Contact SLIM Enterprises today to get the most out of your business.

captcha

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:


Data
400
488
234
2355
234
1600

The procedure we’ll write will remove the bold format from each cell before applying the new formatting criteria.


dim rng as range
set rng=range("a1").currentRegion.columns(1)
dim n as long
n=1500
for x=2 to rng.rows.count

if rng.rows(x).font.bold then

rng.rows(x).font.bold=false
end if

With the bold type removed, the code can now apply the new format criteria.


myValue=rng.rows(x)
If myValue > 1500 Then

rng.Rows(x).Font.Bold = True

rng.Rows(x).Font.ColorIndex = 3
End If
next

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.

With Selection.Font
.Name = "Arial"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 3
.Font.Bold = True

End With

The macro displays a number of formatting properties you can then use in your own code and can be added to your code library.

Summary

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.

Leave a Reply

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

Archive

Categories

Recent News

Recent Comments