Home

Cells Background

 

Introduction

The cell background is the color or pattern that fills its inside. The default background of a cell is white. There are various options available to you if you want to change it.

Once again, the Ribbon provides the quickest means of configuring a cell or a group of cells. To paint a cell or a group with a color other than white, after selecting it, on the Ribbon, click Home. In the Font section, click the arrow of the Fill Color button and select a color.

Practical Learning: Painting the Background of Cells

  1. Change the code as follows:
     
    Sub CreateWorkbook()
        ' CreateWorkbook Macro
        ' This macro is used to create a workbook for the
        ' Georgetown Dry Cleaning Services
    
        ' Keyboard Shortcut: Ctrl+Shift+W
        
        Rem Just in case there is anything on the
        Rem worksheet, delete everything
        Range("A:K").Delete
        Range("1:20").Delete
        
        Rem Create the sections and headings of the worksheet
        Range("B2") = "Georgetown Dry Cleaning Services"
        Range("B2").Font.Name = "Rockwell Condensed"
        Range("B2").Font.Size = 24
        Range("B2").Font.Bold = True
        Range("B2").Font.Color = vbBlue
        
        Range("B3:J3").Interior.ThemeColor = xlThemeColorLight2
        
        Range("B5") = "Order Identification"
        Range("B5").Font.Name = "Cambria"
        Range("B5").Font.Size = 14
        Range("B5").Font.Bold = True
        Range("B5").Font.ThemeColor = 5
        
        Rem To draw a thick line, change the bottom
        Rem borders of the cells from B5 to J5
        Range("B5:J5").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("B5:J5").Borders(xlEdgeBottom).Weight = xlMedium
        Range("B5:J5").Borders(xlEdgeBottom).ThemeColor = 5
        
        Range("B6") = "Receipt #:"
        Range("D6:F6").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("D6:F6").Borders(xlEdgeBottom).Weight = xlHairline
        
        Range("G6") = "Order Status:"
        Range("I6:J6").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("I6:J6").Borders(xlEdgeBottom).Weight = xlHairline
        
        Range("B7") = "Customer Name:"
        Range("D7:F7").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("D7:F7").Borders(xlEdgeBottom).Weight = xlHairline
        
        Range("G7") = "Customer Phone:"
        Range("I7:J7").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("I7:J7").Borders(xlEdgeBottom).Weight = xlHairline
        
        Rem To draw a thick line, change the bottom
        Rem borders of the cells from B5 to J5
        Range("B8:J8").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("B8:J8").Borders(xlEdgeBottom).Weight = xlThin
        
        Range("B9") = "Date Left:"
        Range("D9:F9").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("D9:F9").Borders(xlEdgeBottom).Weight = xlHairline
        
        Range("G9") = "Time Left:"
        Range("I9:J9").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("I9:J9").Borders(xlEdgeBottom).Weight = xlHairline
        
        Range("B10") = "Date Expected:"
        Range("D10:F10").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("D10:F10").Borders(xlEdgeBottom).Weight = xlHairline
        
        Range("G10") = "Time Expected:"
        Range("I10:J10").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("I10:J10").Borders(xlEdgeBottom).Weight = xlHairline
        
        Range("B11") = "Date Picked Up:"
        Range("D11:F11").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("D11:F11").Borders(xlEdgeBottom).Weight = xlHairline
        
        Range("G11") = "Time Picked Up:"
        Range("I11:J11").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("I11:J11").Borders(xlEdgeBottom).Weight = xlHairline
        
        Rem To draw a thick line, change the bottom
        Rem borders of the cells from B5 to J5
        Range("B12:J12").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("B12:J12").Borders(xlEdgeBottom).Weight = xlMedium
        Range("B12:J12").Borders(xlEdgeBottom).ThemeColor = 5
              
        Range("B13") = "Items to Clean"
        Range("B13").Font.Name = "Cambria"
        Range("B13").Font.Size = 14
        Range("B13").Font.Bold = True
        Range("B13").Font.ThemeColor = 5
        
        Range("B14") = "Item"
        Range("D14") = "Unit Price"
        Range("E14") = "Qty"
        Range("F14") = "Sub-Total"
        
        Range("B14:F14").Borders(xlEdgeLeft).LineStyle = xlContinuous
        Range("B14:F14").Borders(xlEdgeLeft).Weight = xlThin
        Range("B14:F14").Borders(xlEdgeTop).LineStyle = xlContinuous
        Range("B14:F14").Borders(xlEdgeTop).Weight = xlThin
        Range("B14:F14").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("B14:F14").Borders(xlEdgeRight).Weight = xlThin
        Range("B14:F14").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("B14:F14").Borders(xlEdgeBottom).Weight = xlThin
        Range("C14").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("C14").Borders(xlEdgeRight).Weight = xlThin
        Range("D14").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("D14").Borders(xlEdgeRight).Weight = xlThin
        Range("E14").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("E14").Borders(xlEdgeRight).Weight = xlThin
    
        Range("B15") = "Shirts"
        Range("B15").Borders(xlEdgeLeft).LineStyle = xlContinuous
        Range("B15").Borders(xlEdgeLeft).Weight = xlThin
        
        Range("H15") = "Order Summary"
        Range("H15").Font.Name = "Cambria"
        Range("H15").Font.Size = 14
        Range("H15").Font.Bold = True
        Range("H15").Font.ThemeColor = 5
        
        Range("B16") = "Pants"
        Range("B16").Borders(xlEdgeLeft).LineStyle = xlContinuous
        Range("B16").Borders(xlEdgeLeft).Weight = xlThin
        
        Range("B17") = "None"
        Range("B17").Borders(xlEdgeLeft).LineStyle = xlContinuous
        Range("B17").Borders(xlEdgeLeft).Weight = xlThin
        
        Range("H17") = "Cleaning Total:"
        Range("I17").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("I17").Borders(xlEdgeBottom).Weight = xlHairline
        
        Range("B18") = "None"
        Range("B18").Borders(xlEdgeLeft).LineStyle = xlContinuous
        Range("B18").Borders(xlEdgeLeft).Weight = xlThin
        
        Range("H18") = "Tax Rate:"
        Range("I18").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("I18").Borders(xlEdgeBottom).Weight = xlHairline
        
        Range("I18") = "5.75"
        Range("J18") = "%"
        Range("B19") = "None"
        Range("B19").Borders(xlEdgeLeft).LineStyle = xlContinuous
        Range("B19").Borders(xlEdgeLeft).Weight = xlThin
        
        Range("H19") = "Tax Amount:"
        Range("I19").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("I19").Borders(xlEdgeBottom).Weight = xlHairline
        
        Range("B20") = "None"
        Range("B20").Borders(xlEdgeLeft).LineStyle = xlContinuous
        Range("B20").Borders(xlEdgeLeft).Weight = xlThin
        
        Range("C15").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("C15").Borders(xlEdgeRight).Weight = xlThin
        Range("C16").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("C16").Borders(xlEdgeRight).Weight = xlThin
        Range("C17").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("C17").Borders(xlEdgeRight).Weight = xlThin
        Range("C18").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("C18").Borders(xlEdgeRight).Weight = xlThin
        Range("C19").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("C19").Borders(xlEdgeRight).Weight = xlThin
        Range("C20").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("C20").Borders(xlEdgeRight).Weight = xlThin
        Range("B14:C14").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("B14:C14").Borders(xlEdgeBottom).Weight = xlThin
        
        Range("B15:C15").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("B15:C15").Borders(xlEdgeBottom).Weight = xlThin
        Range("D15:F15").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("D15:F15").Borders(xlEdgeBottom).Weight = xlHairline
        Range("D15").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("D15").Borders(xlEdgeRight).Weight = xlHairline
        Range("E15").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("E15").Borders(xlEdgeRight).Weight = xlHairline
        Range("F15").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("F15").Borders(xlEdgeRight).Weight = xlThin
        
        Range("B16:C16").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("B16:C16").Borders(xlEdgeBottom).Weight = xlThin
        Range("D16:F16").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("D16:F16").Borders(xlEdgeBottom).Weight = xlHairline
        Range("D16").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("D16").Borders(xlEdgeRight).Weight = xlHairline
        Range("E16").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("E16").Borders(xlEdgeRight).Weight = xlHairline
        Range("F16").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("F16").Borders(xlEdgeRight).Weight = xlThin
        
        Range("B17:C17").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("B17:C17").Borders(xlEdgeBottom).Weight = xlThin
        Range("D17:F17").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("D17:F17").Borders(xlEdgeBottom).Weight = xlHairline
        Range("D17").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("D17").Borders(xlEdgeRight).Weight = xlHairline
        Range("E17").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("E17").Borders(xlEdgeRight).Weight = xlHairline
        Range("F17").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("F17").Borders(xlEdgeRight).Weight = xlThin
        
        Range("B18:C18").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("B18:C18").Borders(xlEdgeBottom).Weight = xlThin
        Range("D18:F18").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("D18:F18").Borders(xlEdgeBottom).Weight = xlHairline
        Range("D18").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("D18").Borders(xlEdgeRight).Weight = xlHairline
        Range("E18").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("E18").Borders(xlEdgeRight).Weight = xlHairline
        Range("F18").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("F18").Borders(xlEdgeRight).Weight = xlThin
        
        Range("B19:C19").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("B19:C19").Borders(xlEdgeBottom).Weight = xlThin
        Range("D19:F19").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("D19:F19").Borders(xlEdgeBottom).Weight = xlHairline
        Range("D19").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("D19").Borders(xlEdgeRight).Weight = xlHairline
        Range("E19").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("E19").Borders(xlEdgeRight).Weight = xlHairline
        Range("F19").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("F19").Borders(xlEdgeRight).Weight = xlThin
        
        Range("B20:F20").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("B20:F20").Borders(xlEdgeBottom).Weight = xlThin
        Range("D20").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("D20").Borders(xlEdgeRight).Weight = xlHairline
        Range("E20").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("E20").Borders(xlEdgeRight).Weight = xlHairline
        Range("F20").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("F20").Borders(xlEdgeRight).Weight = xlThin
        
        Range("H20") = "Order Total:"
        Range("I20").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("I20").Borders(xlEdgeBottom).Weight = xlHairline
        
        Rem Change the widths and heights of some columns and rows
        Rem In previous lessons, we learned all these things
        Range("E:E, G:G").ColumnWidth = 4
        Columns("H").ColumnWidth = 14
        Columns("J").ColumnWidth = 1.75
        
        Rows("3").RowHeight = 2
        Range("8:8, 12:12").RowHeight = 8
        
        Rem Merge the cells H15, I15, H16, and I16
        Range("H15:I16").MergeCells = True
        Rem Align the merged text to the left
        Range("H15:H16").VerticalAlignment = xlBottom
        
        Range("H16").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("H16:I16").Borders(xlEdgeBottom).Weight = xlMedium
        Range("H16:I16").Borders(xlEdgeBottom).ThemeColor = 5
        
        Rem Hide the gridlines
        ActiveWindow.DisplayGridlines = False
    End Sub
  2. Return to Microsoft Excel and press Ctrl + Shift + W to see the result
     
    Georgetown Dry Cleaning Services
  3. Close the worksheet
  4. When asked whether you want to save, click No

The Fill Property Page

As we have seen so far, the Format Cells dialog box provides an extensive set of options for cell configuration. It is equipped with the Fill property page that displays a wide range of colors:

Fill

To change a cell background, use one of the colors on the Background Color section.

 

 
 

Automatic Cell Formatting

 

Introduction

To automatically apply a design on a cell or a group of cells, first select the cells you want to format. Then on the Ribbon, click Home. In the Styles section, if one of the 6 pre-selected formats suits your needs, you can click it

If none of those designs suits you, click the More buttons to display many more options:

As a technique you can use, you can select a group of cells on the same row, apply a style, then select another group of cells on another row, and apply a different style.

Cells Formatting With Styles

A formatting style is a set of font, font size, color, and patterns designed to make a worksheet or one of its sections look good. Microsoft Excel is equipped with categories of styles. You can use those ones or create your own.

When applying a style, you decide to format various cells at the same time using a category of style that has been predefined. Therefore, you apply such a style to a cell or range of cells. By default, when you are typing data in a worksheet, you are already using a predetermined style made of a certain font (Calibri), a font size (11), a border, and background, etc. You can modify this style or create a new one.

To apply a style, you use the Styles section of the Ribbon.

Conditional Formatting

Conditional Formatting allows you to define and apply formatting to some cells, text, and numbers based on criteria that you set. For example, you can format a time sheet to point out whenever an employee gets overtime. You can also use it to track the best sales people in a company by setting a quota that makes a cell range particular.

To use, define, and apply conditional formatting, first select the cells that will be considered. On the Ribbon, click Home. In the Styles section, click Conditional Formatting. A menu would display:

Conditional Formatting

You can use any criteria of your choice. The formatting could be applied to cells' values or a particular formula.

Graphics And Drawing

 

Introduction

Microsoft Excel is equipped with drawing features that can be used to embellish a worksheet. If you have used Microsoft Office long enough, you are probably aware of its drawing tools. They allow you to draw lines, geometric shapes, various flowcharts, connectors, and banners, etc.

Shapes

A shape is an aesthetic figure you draw on a worksheet. Microsoft Excel (in reality Microsoft Office) provides various figures and shapes you can use to enhance the appearance of your worksheet.

To access the shapes, on the Ribbon, click the Insert tab and use the buttons in the Illustration section:

Illustration

To draw a shape, in the Illustration section of the Ribbon, you can click Shapes. A window will display the various shapes that are available:

You can click the desired shape. Then, on the worksheet, click one of the extreme ends, drag to the other extreme, when you get a satisfying size and orientation, release the mouse. Once you release the mouse, the object will still be selected with various object handles of various sides and corners of the object. If you position your mouse on different handles or on the object, the mouse pointers will have different shapes.

This shape Allows you to
Mouse Move Move the whole object
Resize the object vertically
Resize the object horizontally
Resize the object in up-left down-right orientation
Resize the object in down-left up-right orientation
Change the corner of some shapes (is not available for all shapes)

Some objects don’t display all these mouse pointers and some may display different mouse shapes. If/when one of those unusual pointers comes up, you will be guided on its meaning.

Almost any shape you draw has a marking rectangular box around it. This allows you to work on the shape as an object. For example, you can use this box to move the object.

You can move any object to a new location on your screen. Sometimes you will want to select more than one object to manipulate the group. To select more than one object, click one of them, press and hold Shift, then click the other object(s). When you have created the group, release Shift.

A drawn object can be copied and pasted to another location on the same worksheet or to a different worksheet on the same workbook, in another workbook, or even to another document. To copy an object, click it. Then on the Ribbon, click Home. In the Clipboard section, click Copy, and proceed with pasting. You can copy one object or a group of objects. Using the Clipboard window of Microsoft Office 2007, you can copy up to 24 objects at once, then paste them to their new respective locations.

Microsoft Office WordArt

A Microsoft Office WordArt is a fancy formatted sentence whose features you can use to include a good-looking group of words that you type and embed in your worksheet.

To get a WordArt, on the Ribbon, click the Insert tab. In the Text section, click the WordArt button and click the desired format:

A label with Your Text Here would come up. You can then edit it to your liking.

ClipArt and Pictures

You can use pictures to enhance the appearance of your worksheets. You can use almost any kind of picture from almost any format. To get some pictures, you can access the Clip Art section of the Microsoft Office web site. You would have to download those pictures and install them on your computer. You can also use any pictures available to you.

To use a picture, on the Ribbon, click Insert. In the Illustration section, click the Picture button. This would open the Insert Picture dialog box. Locate and select a picture, then click Insert.

Microsoft Excel also allows you to completely change a worksheet’s background with a picture of your choice. To do that, on the Ribbon, click Page Layout. In the Page Setup section click the Background button. From the Sheet Background dialog box, locate and select the desired picture. Then click Insert.

 
 

 

 

Previous Copyright © 2008-2010 FunctionX, Inc. Home