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.dependif 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 Thenrng.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 = TrueFinish 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.