How To Find And Remove Bold Formatting In An Excel Spreadsheet With VBA

How To Find And Remove Bold Formatting In An Excel Spreadsheet With VBA

In case your spreadsheet accommodates undesirable formatting, VBA can selectively take away the codecs and add new standards. A typical state of affairs may be a conditional format based mostly on particular values however now you could apply new situations.

This text explains how one can determine cells with daring sort, take away the formatting.and add new standards.

Figuring out Daring Kind In Every Cell With VBA

The instance we’ll use is a numeric knowledge record that has daring sort the place the worth is over 1000. Now you could enhance the conditional worth to 1500, however change the format to daring, and purple.

The info may look one thing like this:

Information

400

488

234

2355

234

1600

The process we’ll write will take away the daring format from every cell earlier than making use of the brand new formatting standards.

dim rng as vary

set rng=vary("a1").currentRegion.columns(1)

dim n as lengthy

n=1500

for x=2 to rng.rows.depend
if rng.rows(x).font.daring then
rng.rows(x).font.daring=false

finish if

With the daring sort eliminated, the code can now apply the brand new format standards.

myValue=rng.rows(x)

If myValue > 1500 Then
rng.Rows(x).Font.Daring = True
rng.Rows(x).Font.ColorIndex = three

Finish If

subsequent

The code now produces an output which shows all values higher than 1500 in daring and purple sort.

Such a process can simply be developed so as to add different formatting sorts. For instance, to extend the font measurement, the scale property could be accessed.

 

rng.Rows(x).Font.Measurement = 16

It may be helpful to know the totally different properties out there when working with cell codecs. A method to do that is by recording a macro. Begin recording, after which choose a cell worth and alter a number of of the settings similar to shade and measurement. Should you view the ensuing macro you may see one thing like this.

With Choice.Font

.Title = "Arial"

.Measurement = 10

.Strikethrough = False

.Superscript = False

.Subscript = False

.OutlineFont = False

.Shadow = False

.Underline = xlUnderlineStyleNone

.ColorIndex = three

.Font.Daring = True
Finish With

The macro shows quite a lot of formatting properties you may then use in your personal code and could be added to your code library.

Abstract

Though Excel has some robust formatting instruments, VBA allows builders to customise an answer to go well with their very own wants. By including this sort of process to a code library it may possibly enhance productiveness and effectivity to any new challenge.