VBA Case Statements

Here is a screenshot of the final Excel file in the series. Essentially, the working parts are just the first two columns. The user enters a score in the first column and the VBA code matches the inputted score with some associated font and cell characteristics and alters the cell into which the score has been entered accordingly. A grade is also assigned to the mark as soon as it is entered into the cell and placed in the next column.

Such font and cell formatting can be done with the "Conditional Formatting" worksheet function in both Excell 2003 and 2007, however, despite the increased flexibility of this function in Excel 2007 it is still no match for VBA code in which no limit is placed upon the number of formatting operations that can be carried out. Notice also that the assigned grades do not need a "Lookup" table to be constructed somewhere else on the worksheet as they are controlled by the VBA code and consequently no formulae need to be typed into the cells of column B (and therefore cannot be accidentally deleted!)

Lesson Text
Notice that at the end of this file is an appendix from which the VBA code may be highlighted, copied and pasted into the Visual Basic Editor (VBE) to save yourself some time.

Excel Files (1), (2), (3), (4), (5), (6), (7), (8)

My advice is for you to download the files directly to your hard drive by right-clicking on the link and then selecting "Save Target As..." - otherwise the Excel files may not work fully in your browser window!