VBA Code

Shading Cells

The best way to shade cells is to define the ColorIndex property and assign it to the corresponding colour palette number.

Range("A1:B10").Interior.ColorIndex = 17 

Range("A1:B10").Interior.ColorIndex = xlColorIndex.xlColorIndexAutomatic 
Range("A1:B10").Interior.ColorIndex = Excel.xlAutomatic

The second line is only made available for backwards compatibility.

objRange.ClearContents  'values and formulas  
objRange.Clear 'values, formulas and formatting
objRange.ClearFormats 'clears just formatting (inc conditional formatting)

Using Colours

Range("A1:B10").Interior.Color = RGB(153,51,102) 

Removing Shading

Range("A1:B10").Interior.ColorIndex = xlColorIndex.xlColorIndexNone 
Range("A1:B10").Interior.ColorIndex = Excel.xlNone

The second line is only made available for backwards compatibility.

Adding Patterns

The default Pattern is solid.

Range("A1:B10").Interior.Pattern = xlPattern.xlSolid 

Merging Cells

Range("A1:B1").MergeCells = True

Also see the Format Dialog Box page

MergeArea Property

Returns a Range object that represents the merged range containing the specified cell
If the specified cell isn't in a merged range, this property returns the specified cell
This property only works on a single cell range

Dim objMergeArea As Range 
Set objMergeArea = Range("A3").MergeArea
If objMergeArea.Address = "$A$3" Then
   Call MsgBox (not merged")
   objMergeArea.Cells(1,1).Value = "hello"
End If

Wrapping Text

Range("A1").WrapText = True 


Range("A2").Font.Italic = False 
Range("D10").Font.Bold = True
Rows(3).Font.Bold = True
Cells(2,4).BorderAround Weight:=xlMedium
ActiveSheet.Rows(5).Font.Size = 10
ActiveSheet.Rows(2:10).Font.Name = "Arial"

The available font size using VBA is 1 to 127 although the formatting toolbar only lists 8 to 72. If you do not have the chosen font installed then Excel will substitute the closest match.
When formatting text, there is a font.fontstyle property but I would not suggest using this. It is better to explicitly specify whether bold, italics etc.

When you change the background or font colour of a cell, Excel does not consider this to be changing the value of the cell and will not generate a Worksheet_Change() event.

Clears the formulas and formatting



The Colors property returns or sets the colours defined in the colour palette.
The colour palette has 56 colours.

ActiveWorkbook.Colors(1) = RGB(50, 100, 150) 

If the Index property is not specified then the Colors property will return an array containing all 56 colours.

Dim avArray As Variant 
   avArray = ActiveWorkbook.Colors()

You can quickly reset all the colours in a workbook.


© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited