Format Cells Dialog Box


Number tab

Range.NumberFormat = "@" 
Range.NumberFormat = "General"

Application.DecimalSeparator 
UseSystemSeparators

Alignment tab

With Selection 
    .HorizontalAlignment = xlHAlign.xlHAlignGeneral
    .VerticalAlignment = xlVAlign.xlVAlignBottom
    .AddIndent = False
    .IndentLevel = 0 - 15

    .WrapText = False
    .ShrinkToFit = False
    .MergeCells = False

    .Orientation = 40

'for some reason this is not an enumeration ?
    .ReadingOrder = Excel.Constants.xlRTL
    .ReadingOrder = Excel.Constants.xlLTR
    .ReadingOrder = Excel.Constants.xlContext
End With

Font tab

The Normal font checkbox just returns all the properties back to the default values.
Some of these properties can also return Nothing (and not just True or False). It is safer to use variant variables when analysing and changing the properties.

With Selection.Font 
    .Name = "Arial"
    .FontStyle = "Regular" '(in C# this does not exist as there are specific properties for Bold and Italic
    .Size = 10
    .Bold = False
    .Underline = xlUnderlineStyle.xlUnderlineStyleNone

    .Color = ?
'when you select Automatic the color index used is 0
    .ColorIndex = xlColorIndex.xlColorIndexAutomatic | 0 | 53

    .Strikethrough = False
    .Superscript = False
    .Subscript = False

'This is true if the font selected in an outline font - This has no effect in Windows
    .OutlineFont = False

'This is true if the font selected in an outline font - This has no effect in Windows
    .Shadow = False
End With

Border tab

You can use either ColorIndex or Color, but not both.
You can specify both the LineStyle and the Weight. If you do not specify them then the default will be used.


linestyle=xlNonelinestyle=xlDashDotDot; weight=xlMedium
linestyle=xlContinuous; weight=xlHairlinelinestyle=xlSlantDashDot; weight=xlMedium
linestyle=xlDot; weight=xlThinlinestyle=xlDashDot; weight=xlMedium
linestyle=xlDashDotDot; weight=xlThinlinestyle=xlDash; weight=xlMedium
linestyle=xlDashDot; weight=xlThinlinestyle=xlContinuous; weight=xlMedium
linestyle=xlDash; weight=xlThinlinestyle=xlContinuous; weight=xlThick
linestyle=xlContinuous; weight=xlThinlinestyle=xlDouble; weight=xlThick

Selection.Borders(xlBordersIndex.xlEdgeLeft).LineStyle = xlLineStyle.xlContinuous 
Selection.Borders(xlBordersIndex.xlEdgeLeft).Weight = xlBorderWeight.xlThin
Selection.Borders(xlBordersIndex.xlEdgeLeft).ColorIndex = xlColorIndex.xlColorIndexAutomatic | 53
Selection.Borders(xlBordersIndex.xlEdgeLeft).Color = RGB(10,10,10)

Selection.Borders(xlBordersIndex.xlEdgeTop).LineStyle = xlLineStyle.xlContinuous
Selection.Borders(xlBordersIndex.xlEdgeTop).Weight = xlBorderWeight.xlThin
Selection.Borders(xlBordersIndex.xlEdgeTop).ColorIndex = xlColorIndex.xlColorIndexAutomatic | 53

Selection.Borders(xlBordersIndex.xlEdgeBottom).LineStyle = xlLineStyle.xlContinuous
Selection.Borders(xlBordersIndex.xlEdgeBottom).Weight = xlBorderWeight.xlThin
Selection.Borders(xlBordersIndex.xlEdgeBottom).ColorIndex = xlColorIndex.xlColorIndexAutomatic | 53

Selection.Borders(xlBordersIndex.xlEdgeRight).LineStyle = xlLineStyle.xlContinuous
Selection.Borders(xlBordersIndex.xlEdgeRight).Weight = xlBorderWeight.xlThin
Selection.Borders(xlBordersIndex.xlEdgeRight).ColorIndex = xlColorIndex.xlColorIndexAutomatic | 53

You cannot define an InsideVertical border if the Range has no inside vertical borders (i.e. only 1 cell).
Assigning an InsideVertical on a single cell will generate an error.

Selection.Borders(xlBordersIndex.xlInsideVertical).LineStyle = xlLineStyle.xlContinuous 
Selection.Borders(xlBordersIndex.xlInsideVertical).Weight = xlBorderWeight.xlThin
Selection.Borders(xlBordersIndex.xlInsideVertical).ColorIndex = xlColorIndex.xlColorIndexAutomatic | 53

You cannot define an InsideHorizontal border if the Range has no inside vertical borders (i.e. only 1 cell).
Assigning an InsideHorizontal on a single cell will generate an error.

Selection.Borders(xlBordersIndex.xlInsideHorizontal).LineStyle = xlLineStyle.xlContinuous 
Selection.Borders(xlBordersIndex.xlInsideHorizontal).Weight = xlBorderWeight.xlThin
Selection.Borders(xlBordersIndex.xlInsideHorizontal).ColorIndex = xlColorIndex.xlColorIndexAutomatic | 53

Selection.Borders(xlBordersIndex.xlDiagonalDown).LineStyle = Excel.xlNone
Selection.Borders(xlBordersIndex.xlDiagonalUp).LineStyle = Excel.xlNone

You can also remove all the borders using this single line

Selection.Borders.LineStyle = xlLineStyle.xlLineStyleNone 

Fill tab

This was the Patterns tab in 2003.

Selection.Interior.ColorIndex = (old 2003) 

There is a Patterns property that corresponds to the Patterns tab on this dialog box.

Selection.Interior.Pattern = xlPattern.xlPatternNone 
Selection.Interior.Pattern = xlPattern.xlLightDown

The other properties are only relevant when the Interior.Pattern property is not xlPatternNone.


The PatternColorIndex property can be either an index into the current colour palette, or as one of the following xlColorIndex constants

Selection.Interior.PatternColorIndex = 42 

This can be used when you don't want a pattern. This is the same as setting the Interior.Pattern property to xlPatternNone

Selection.Interior.PatternColorIndex = xlColorIndex.xlColorIndexNone 

This can be used to select the automatic pattern.

Selection.Interior.PatternColorIndex = xlColorIndex.xlColorIndexAutomatic 

Selection.Interior.ThemeColor = xlThemeColor.xlThemeColorAccent1 
Selection.Interior.TintAndShade = -0.24994659

Selection.Interior.PatternThemeColor = xlThemeColor.xlThemeColorAccent2 
Selection.Interior.PatternTintAndShade =
Selection.Interior.Gradient.Degree = 90
Selection.Interior.Gradient.ColorStops.Clear
Selection.Interior.Gradient.ColorStops.Add(0).ThemeColor =
Selection.Interior.Gradient.ColorStops.Add(0).TintAndShade =

Protection tab

Selection.Locked = False 
Selection.FormulaHidden = True

Please refer to the Protection section for more details.


© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext