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)  
objRange.ClearNotes 
objRange.ClearComments 
objRange.ClearOutline 

Using Colours

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

Checking for any shading

If (ActiveCell.Interior.ColorIndex <> xlNone) Then 

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").Merge 
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") 
Else 
   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

objRange.Clear 

Application.FindFormat 
Application.ReplaceFormat 

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.

ActiveWorkbook.ResetColors