PDA

View Full Version : Help adding font size / color / etc to my macro



blewth
12-07-2016, 04:37 PM
Hi all

I have a few macros that I would like to adjust... I have inherited these macros and thus am a bit unfamiliar with how it all works, so any help would be greatly appreciated..

--------------------------------------------------------------------------------------------

Macro 1 - Blue fill

To add:
Fill color: 68 / 105 / 125
Size - 10
Font - Book Antiqua
Style - Bold
Font Color - White

Code:

With Selection.Interior
.ColorIndex = 49
.Pattern = xlSolid
End With
End Sub


Macro 2 - Grey fill

To add:
Fill color: 165 / 157 / 149
Size - 10
Font - Book Antiqua
Style - Bold
Font Color - Black

Code:

With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
End Sub



Macro 3 - Light brown fill
To add:
Fill Color - 225 / 222 / 174
Size - 10
Font - Book Antiqua
Style - Bold
Font Color - Black

Code:

With Selection.Interior
.ColorIndex = 19
.Pattern = xlSolid
End With
End Sub



Macro 4 - Book Antiqua font

To add:
Size - 10

Code:

With Selection.Font
.Name = "Book Antiqua"
End With
End Sub

-----------------------------------------------------------------------------------


Thank you all so much for your help

offthelip
12-07-2016, 05:17 PM
It works fine on my machine,

SamT
12-07-2016, 06:52 PM
All that code relates to the Range Object Model.

Ranges have certain Properties that can be set with VBA


With Range
With .Interior
'The numbers of the ColorIndex Property correspond to the colors seen in the Format Cells Menu, Patterns Tab.
.Color = RGB(0, 1, 256) '(The colors groups in your post) 'Look up how to use RGB
.ColorIndex = 49 '(1 to 56 or a Constant are allowed.
.Pattern =
.PatternColor =
.PatternColorIndex =
End With 'Interior

With Font
.Color =
.Size =
.Name =
.Bold =
End With 'Font
End With 'Range

Note that the keyword "With" mean "with the next dotted Property."

With Range
With.interior
'Blah, Blah
With .Font
.Color

A really long way to do for each Property is:

Range.Font.Color =
Range.Font.Size =
Range.Font.Name =
'Etc=

In the VBA Editor on your machine, press F2. Then enter any of those Objects and Properties into the Search window and follow the hints to the source. at the bottom, you will find more hints.