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) 


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 



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

Top

PrevNext