Starting with Excel VBA, Writing VBA Code
———————————————————–
Contents:
Excel VBA Objects, Properties & Methods
Line Continuation within VBA code
Using Message Box in your code
———————————————————–
In Excel, a set of vba codes are called macros or procedures. VBA is a programming language used to work with Microsoft Excel.
VBA is a programming language used to work with Microsoft Excel, and also with other Microsoft Office applications like Microsoft Word, Microsoft Access, PowerPoint, … and with many third-party applications. It is a language which Excel understands and is used to give instructions to Excel to program and automate tasks. VBA programming is used to:- get enhanced functionality which might be beyond an Excel spreadsheet; to automate repetitive tasks in Excel; and, to integrate Excel with other Office applications such as Microsoft Access. The instructions given to Excel are in the form of a set of codes, which are called macros or procedures. The VBA code is written in the Visual Basic Editor (VBE), which is the the VBA development environment ie. integrated development environment (IDE), wherein you create a VBA project.
We briefly explain some basic concepts in writing code in VBA Excel, and then proceed to create some basic VBA Codes/Procedures.
VBA Objects, the Visual Basic Editor (VBE), Declaring Variables and VBA Procedures have been dealt in detail in separate sections. For a comprehensive understanding on these, click below on the related links to these sections:
Excel VBA Objects, Properties & Methods.
The Visual Basic Editor (VBE) in Excel VBA.
Declaring Variables, Using Dim Statement, Variable Names & Data Types in Excel VBA.
Excel VBA Procedures, Naming Rules, Public & Private Scope, Placement in Modules, Call & Execute.
Excel VBA Objects, Properties & Methods:
An object is a thing which contains data and has properties and methods. Properties are the characteristics or attributes that describe the object (like name, color, size) or define an object’s behaviour (viz. if visible or enabled). An object’s data or information can be accessed with properties (viz. Value property, Name property). A Method is an action performed by an object. Calling a Method will execute a vba code which will cause the object to perform an action. You can associate objects with nouns, properties with adjectives and methods with verbs. An object could be a house, car, table or pen. Properties of a car include its color or size, which describe it. A car can perform actions of moving, accelerating or turning which are its methods. Examples of objects in Excel are workbook, worksheet, range, command button, font, etc. A Range object has “Value” as one of its properties and “Select” as one of its methods. Similarly a worksheet has, among others, a “Name” property, a “Delete” method, and a “Copy” method having arguments which contain information in respect of the worksheet to be copied.
The Object Model of the Application (Excel) refers to and contains its programming objects which are related to each other in a hierarchy. The entire Excel application is represented by the Application Object which is at the top of the Excel object hierarchy and moving down you can access the objects from Application to Workbook to Worksheet to Range (Cells) and further on, by connecting the objects with a period (dot). Excel objects are accessed through ‘parent’ objects – Worksheet is the parent of the Range Object, and the Workbook is the parent of the Worksheet object, and the Application object is the parent of the Workbook object.
Objects also have event procedures attached to them. Events are actions performed, or occurences, which trigger a VBA code or macro. An event procedure (ie. a vba code) is triggered when an event occurs such as opening / closing / saving / activating / deactivating the workbook, selecting a cell or changing cell selection in a worksheet, making a change in the content of a worksheet cell, selecting or activating a worksheet, when a worksheet is calculated, and so on. Excel provided built-in event procedure – an Event Procedure is automatically invoked when an object recognizes the occurrence of an event. Event procedures are attached to objects like Workbook, Worksheet, Charts, Application, UserForms or Controls. Event Procedures are triggered by a predefined event and are installed within Excel having a standard & predetermined name viz. like the Worksheet change procedure is installed with the worksheet – “Private Sub Worksheet_Change(ByVal Target As Range)”. In the Worksheet Change event procedure, the Worksheet object is associated with the Change event, which means that with the worskheet change event, a sub-procedure containing customized code runs automatically when you change the contents of a worksheet cell. Custom Events – you can also define your own events in custom classes (class modules), and create event procedures that run when those events occur.
VBE is contained in the Microsoft Excel workbook, and is an environment used to write, edit and debug VBA code. To launch VBE in Excel 2007: (i) On the Developer tab, in the Code group, click Visual Basic to display VBE. (ii) On the Developer tab, in the Controls group, click View Code to display VBE. (iii) Right Click the active worksheet tab at the bottom, then click on View Code to display VBE. (iv) Press Alt+F11 keys combination to switch back and forth between workbook environment and VBE. The Visual Basic Editor components refer to the Code Window, Project Explorer, Properties Window & The Programming Workspace (ie. Menus and Toolbars, Object drop-down Box and Procedure drop-down Box, The Immediate Window, The Locals Window & The Watch Window). Code Window: This is where you write and edit your code and procedures, and also where macros get recorded by you. Project Explorer: The Project Explorer displays the list of all existing projects. It gives a tree-view wherein you can collapse to hide or expand to view the objects, userforms and modules contained in a project. Each project contains: an Objects folder, which is the Microsoft Excel Objects folder; Forms folder if it contains UserForms; Modules folder if it contains Modules; and Class Modules folder if it contains Classes. The Objects folder is always present and contains: a sheet object for each existing worksheet, and a ThisWorkbook object. Each sheet object has as its first name appearing outside the parentheses which is the sheet code name, and the second name which appears after the code name and within the parentheses is the sheet tab name which appears on the tab of the Excel worksheet. To display the Project Explorer Window, click View on the VBE Menu Bar and then select Project Explorer, or press CTRL+R in VBE.
Macros (viz. vba codes or sub-procedures) should reside in their appropriate modules or Excel will not be able to find and execute them. The Object Modules are used for Excel built-in event procedures and to create your own events. Object Modules in Excel are: ThisWorkbook module, Sheet modules (worksheets and chart sheets), UserForm modules and Class modules. General vba code in respect of events not associated with a particular object (like workbook or worksheet) are placed in the Standard Code Module. A generally accepted practice is to place event procedures in the ThisWorkbook module, Sheet modules and UserForm modules, while placing other vba codes in Standard Code modules. Standard Code Modules: These are also referred to as Code Modules or Modules, and there can be any number of these Modules (Module1, Module2, …) in a VBA project, wherein each Module can be used for covering a certain aspect of the project. To insert a Module click Insert on the VBE Menu Bar and then select Module.
A VBA procedure, also referred to as a Macro, is defined as a set of codes which make Excel perform an action. A procedure is usually of two types, a sub-procedure (viz. sub-routine) or a function. The third type of procedure is Property, used for Class Modules. A VBA project can contain multiple modules, class modules and user forms. Each module contains one or more procedures viz. sub-procedures or functions. Procedures break a program into smaller and specific components.
A sub-procedure declaration statement starts with the keyword Sub, followed by a name and then followed by a set of parentheses. A sub-procedure ends with an End Sub statement which can be typed but it also appears automatically on going to the next line after the Sub declaration statement is typed. Your vba code or statements are entered inbetween.
There are many ways to run a macro, using the Macro dialog box or Shortcut key or in VBE or by assigning macro to an object, we briefly discuss some of them. Macro dialog box: Under the View tab on the ribbon, click Macros in the Macros group, click View Macros which will open the Macro dialog box. In the Macro dialog box, select the Macro name and click Run to execute the Sub/macro. You can also open the Macro dialog box by clicking on Macros in the Code group under the Developer tab on the ribbon. Visual Basic Editor: 1) In the Visual Basic Editor, click within the sub procedure and press F5 (or click Run in the menu bar and then click Run Sub/Userform). 2) In the Visual Basic Editor, click Tools in the menu bar, then click Macros which opens the Macros dialog box. In the Macros dialog box, select the Macro name and click Run to execute the macro.
A good way to run a macro would be to click on a button bearing a self-explanatory text “Calculate Bonus” appearing on the worksheet or clicking a Toolbar Button. For this you have to assign the macro to an object, shape, graphic or control. Assign Macro to a Form Control viz. Button: You can assign a macro to any Form control. On the Developer tab on the ribbon, click Insert in the Controls group, choose and click Button in Form Controls and then click on your worksheet where you want to place the the upper-left corner of the Button (you can move and resize later). Right click on the Button and select Assign Macro which opens the Assign Macro dialog box from where you can select and assign a macro to the Button. Assign Macro to any Object, Shape, Picture, Chart, Text Box, etc. inserted in the worksheet: Under the Insert tab on the ribbon, you can insert an Object, Shape, Picture, SmartArt graphic, Chart, Text Box, WordArt, etc. in the worksheet. Right click on the object, shape or graphic you have inserted, and select Assign Macro which opens the Assign Macro dialog box from where you can select and assign a macro to the Button.
Line Continuation within VBA code:
Many a times while writing code in VBA, a single line of code might get very long and in the code window you will need to scroll to the right side to read it in full. To split a single line into multiple lines in VBA, you can use the Line Continuation Character which is the combination of a space followed by an underscore ( _). Using this as a line break, will enable you to continue to the next line and so on, and this will treat all these continued lines as a single line in your VBA code. Note that you can have a maximum of 25 lines physically, joined with the Line-Continuation character (meaning a maximum of 24 Line-Continuation characters), to be treated as a single logical line of code. A physical line can have a maximum of 1,023 characters while a logical line can have a maximum of 10,230 characters, so that a maximum of 25 physical lines can be joined totalling to a maximum of 10,230 characters.
Example: The first code uses the Line Continuation Character to break a single line into 2 lines, whereas the second code is the same in a single line.
‘Add a new worksheet named “NewSheet” before the Worksheet named “Sheet2”:
Worksheets.Add(Before:=Worksheets(“Sheet2”)).Name = “NewSheet”
‘Add a new worksheet named “NewSheet” before the Worksheet named “Sheet2”:
Worksheets.Add(Before:=Worksheets(“Sheet2”)).Name = “NewSheet”
While writing code in VBA, the code which has a syntax error will turn red as soon as the cursor moves off the line. If the Auto Syntax Check is selected in VBA (default setting), a syntax check is done every time you move your cursor to a new line, and in case of syntax error a message box will pop up telling you about a “Compile Error”. This is quite annoying to many developers as it disrupts the workflow as each pop up message box has to be specifically dismissed. If the Auto Syntax Check is deselected, you will no longer get these pop up message boxes with the “Compile Error” though the error syntax will still turn red. You can deselect the Auto Syntax Check by going to VBE, click on Tools > Options which displays the Options dialog box, select the Editor tab, and then deselect the check box. You can also change the default red color of the Syntax Error by going to VBE, click on Tools > Options which displays the Options dialog box, in the Editor Format tab, select Syntax Error Text in the Code Colors list box, and then choose the new Foreground color.
Within a Procedure, while writing code you can simultaneously provide comments to explain the purpose and what the code is doing. To differentiate your comments with the code, you will identify the comments by preceding them with either a single apostrophe character (‘) or with Rem followed by a space. Comment text will not be considered and will be ignored by VBA so that a syntax error is not returned. It is strongly recommended to use comment text in your code which will be of immense help to another user in understanding, or if you might want to edit at a later date. Multiple comment lines: Once you add a comment character (‘) at the beginning of a comment line, you can use the continuation sequence (_) character to continue the comment to the next line. When you press the Enter key after typing a comment, its color will turn green, as per VBA’s default setting. This default setting of the Comment Text’s color can be changed by going to VBE, click on Tools > Options which displays the Options dialog box, in the Editor Format tab, select Comment Text in the Code Colors list box, and then choose the new Foreground color. Note that comment text need not necessarily start at the beginning of a line, it can be inserted on the right side in a code line, by leaving a few spaces after the code and then typing a single apostrophe followed by your comment.
Refer Image 1, which shows different ways to Comment Text (appearing in Green text), with the last line displaying a syntax error (red color).
Your VBA Procedure may consist of multiple lines of code with a series of statements. As your code gets lengthier and more complex, formatting the code with indentation will help make it easier to read, and it will also make debugging simpler. Indenting means moving code to the right side. Developers typically use indenting for code within the beginning and end lines of loops such as If…Else…End If, For…Next, etc. wherein the series of statements between a For..Next loop are indented to distinguish them belonging to the particular loop. Indenting is particulalry useful in nested code so that each block of code is visually separated, and the beginning and end lines of each block are exactly lined up. Indenting is usually done by pressing the Tab key once or multiple times as required, before typing your statement. Note that by default in VBA, pressing the Tab key moves four spaces or characters to the right. This default setting of the Tab’s value can be changed by going to VBE, click on Tools > Options which displays the Options dialog box, select the Editor tab, and enter the new value in the “Tab Width” box. In the Editor tab of the Options dialog box, you can also select “Auto Indent” which will repeat the indenting of the current line on pressing Enter.
Refer Image 2, which gives an example of indenting vba code containing loops.
A variable is a named storage location used to store temporary values or information for use in execution of the code. In your vba program, a variable stores data and its content is used or changed later while executing the code. By declaring a variable for use in your code, you tell the Visual Basic compiler the variable’s data type (type of value it represents viz. integer, decimal, text, boolean, etc.) and other information such as its scope/level (what code can access it – variables can be Procedure Level, Module Level or can have a Public scope). Variables must be explicitly declared using the Dim, Private, Public, ReDim, or Static statements. When you declare variables by using a Dim statement (Dim is short for dimension): for declaring a variable to hold an Integer value, use “Dim rowNumber As Integer”; for declaring a variable to hold text values, use “Dim strEmployeeName As String”; and so on.
Keywords are reserved words that VBA uses as part of its programming language. Keywords are words or commands that are recognized by VBA and can be used in vba code only as part of the vba language (like in a statement, function name, or operator) and not otherwise (like sub-procedure or variable names). Examples of keywords are: Sub, End, Dim, If, Next, And, Or, Loop, Do, Len, Close, Date, ElseIf, Else, Select, and so on. To get help on a particular keyword, insert your mouse cursor within the keyword (in your vba code in VBE ) and press F1. Note that Keywords get capitalized in the vba code indicating that they have been written correctly viz. typing next will automatically appear as Next.
In VBA, you can perform calculations with numeric values by using the arithmetic operators. For Addition, use the + sign, to add values. For Multiplication, use the * sign, to multiply one value with another. For Subtraction, use the – sign, to calculate the difference between two numbers by subtracting one from another. For Negation also, use the – sign, to write a single negative number. For Division, use the forward slash “/”, to divide two values. For Integer Division, use the backlash operator “\”, where both the dividend and the divisor must be integers (or converted to integers), and the result will also be an Integer number ignoring the decimal part meaning that Integer division returns the quotient and ignores any remainder, for example, when dividing 7 by 3, 7 is called the dividend and 3 the divisor, the quotient is 2, and the remainder is 1 – Integer Division will return the number 2 and ignore the remainder. For Exponentiation, use the ^ operator, to raise the power of a number to another number corresponding to repeated multiplication. You can use parentheses with arithmetic operators, for example, if you want to first add 5 and 3 and then multiply the reult by 7, you will type (5 + 3) * 7, or else using 5 + 3 * 7 will do the multiplication before the addition due to operator precedence.
In VBA, the concatenate operator, ampersand (&), is used to concatenate multiple strings into a single string. Take 2 strings “Ex” and “cel”, use the concatenate operator (&), to get a single string “Excel”. (“Ex” & “cel” = “Excel”). The concatenate operator is often used in vba code, to join or connect multiple text strings into a single text string.
Using Message Box in your code:
A MsgBox Function is often used in the vba code to display a message in a dialog box, wherein user response is required by clicking an appropriate button (viz. Ok, Cancel, Yes, No, Retry, Ignore or Abort). A message box is also commonly used as a debugging tool, to validate or check code for any errors. The message box is a means of interacting with the user viz. to display a value returned by executing a statement or code, or if you want the code to confirm from the user before performing an action like deleting or saving something, or if you want to let the user know that the macro has finished executing, and so on. The simplest code for displaying a message box is with the vba statement – MsgBox “Hello” – when your code is run, a dialog box will appear displaying the “Hello” message with an “Ok” button, clicking which the message box will be dismissed and code execution will continue. The message you wish to display in the message box should be typed between the double-quotes viz. “Hello” in this example.
See below examples of writing vba code.
Example 1: Writing vba code not-using / using the With…End With Statements (enter text in Range, format cells & fonts) – refer Image 3. Also see Example 5.
Sub vbaCode1a()
‘writing vba code without using the With…End With Statements – refer Image 3
‘enter text “hello” in Range A1:C5 of worksheet named “Sheet1” in ThisWorkbook, using the Value property of the Range object:
ThisWorkbook.Worksheets(“Sheet1”).
Range(“A1:C5”).Value = “hello”
‘use the Name / Size / Italic property of the Font object to set the font:
ThisWorkbook.Worksheets(“Sheet1”).
Range(“A1:C5”).Font.Name = “Times New Roman”
ThisWorkbook.Worksheets(“Sheet1”).
Range(“A1:C5”).Font.Size = 12
ThisWorkbook.Worksheets(“Sheet1”).
Range(“A1:C5”).Font.Italic = True
ThisWorkbook.Worksheets(“Sheet1”).
Range(“A1:A5”).Font.Bold = True
‘Use the Interior property of the Range object to return the Interior object. Then using the ColorIndex Property set the color for the interior of all cells in the range to yellow.
ThisWorkbook.Worksheets(“Sheet1”).
Range(“A1:A5”).Interior.ColorIndex = 6
‘set font color to blue, using ColorIndex Property of the Font object:
ThisWorkbook.Worksheets(“Sheet1”).
Range(“B1:B5”).Font.ColorIndex = 5
‘set font color to red:
ThisWorkbook.Worksheets(“Sheet1”).
Range(“C1:C5”).Font.ColorIndex = 3
End Sub
For live code of this example, click to download excel file.
Sub vbaCode1b()
‘writing the same vba code as above using the With…End With Statements – make your code more readable using a With Block, and with Nesting ie. Block within a Block:
‘refer Image 3
With ThisWorkbook.Worksheets(“Sheet1”)
With .Range(“A1:C5”)
.Value = “hello”
With .Font
.Name = “Times New Roman”
.Size = 12
.Italic = True
End With
End With
With .Range(“A1:A5”)
.Font.Bold = True
‘set font color to yellow:
.Interior.ColorIndex = 6
End With
‘set font color to blue:
.Range(“B1:B5”).Font.ColorIndex = 5
‘set font color to red:
.Range(“C1:C5”).Font.ColorIndex = 3
End With
End Sub
Example 2: Use a message box to return the number of open workbooks, names of ThisWorkbook / active workbook / active worksheet.
Sub vbaCode2()
‘use a message box to return the number of open workbooks, names of ThisWorkbook / active workbook / active worksheet:
‘counts the number of open workbooks
MsgBox Workbooks.Count
‘counts the number of worksheets in ThisWorkbook (the workbook containing your code)
MsgBox ThisWorkbook.Worksheets.Count
‘returns the name of ThisWorkbook
MsgBox ThisWorkbook.Name
‘returns the Active Workbook which may or may not be ThisWorkbook (in which your code is entered)
MsgBox ActiveWorkbook.Name
‘returns the Active Sheet in the current Active Workbook
MsgBox ActiveSheet.Name
End Sub
Example 3: Writing a vba code – refer Image 4
Sub vbaCode3()
‘enter this code in a standard module – refer Image 4 to see the result of executing below code
‘Activate ThisWorkbook to write your code here. Note that it is required to activate ThisWorkbook in case multiple workbooks are open.
ThisWorkbook.Activate
‘make worksheet named “Sheet1” the active sheet in the active workbook, using the Activate Method applied to a Worksheet Object:
Worksheets(“Sheet1”).Activate
‘refer active sheet – using the ActiveSheet Property to return the active sheet in the active workbook
With ActiveSheet
‘use the Range Property to refer a Range object
With .Range(“A1”)
‘enter a heading (“VBA Objects”) in cell A1, using the Value property of the Range object to set the value for the range:
.Value = “VBA Objects”
‘set color of cell A1 to Yellow:
‘Use the Interior property of the Range object to return the Interior object. Then using the Color Property set the color for the interior of cell A1 to yellow.
.Interior.Color = vbYellow
‘use the Bold property of the Font object to set the font to bold:
.Font.Bold = True
‘use the Range.HorizontalAlignment Property to set the horizontal alignment (center align) for the Range object (cell A1):
.HorizontalAlignment = xlCenter
End With
‘the Name Property of an object returns the Object’s name:
‘enter ThisWorkbook’s (ThisWorkbook has been activated) name in cell A2:
.Range(“A2”).Value = “ActiveWorkbook Name: “ & ActiveWorkbook.Name
‘enter active sheet name in cell A3:
.Range(“A3”).Value = “ActiveSheet Name: “ & ActiveSheet.Name
‘make cell A4 the active cell, using the Activate Method applied to a Range Object to activate a cell:
.Range(“A4”).Activate
‘enter cell address (using Address Property) in the active cell (A4); the ActiveCell property returns a Range object representing the currently active cell.
.Range(“A4”).Value = “ActiveCell Address: “ & ActiveCell.Address
‘refer to Column A of the active sheet
With Columns(“A”)
‘refer to the Font object in column A, using the Font property to return the Font object
With .Font
‘use the Name property of the Font object to set the font name:
.Name = “Arial”
‘use the Size property of the Font object to set the font size to 10 points:
.Size = 10
‘use the Color property of the Font object to set the font color to blue:
.Color = vbBlue
End With
‘use the AutoFit Method of the Range object to get the best fit for column width (column A):
.AutoFit
End With
End With
End Sub
Example 4: Do calculations in vba code with arithmetic operators, not-using / using variables – refer Image 5
Sub vbaCode4a()
‘Do calculations in vba code with arithmetic operators, without using variables – refer Image 5
‘refer ThisWorkbook (the workbook containing your code)
With ThisWorkbook
‘using the Add Method applied to a Worksheets Collection object, specifying the named argument (After) – adds a new worksheet after the last Worksheet
‘the last worksheet in ThisWorkbook is returned by the count of the number of worksheets
Worksheets.Add After:=Worksheets(Worksheets.Count)
‘the new worksheet becomes the ActiveSheet:
With .ActiveSheet
.Range(“A1”).Value = “Maths Score”
.Range(“B1”).Value = “English Score”
.Range(“C1”).Value = “Average Score”
‘set the number format of the range, using the NumberFormat property:
.Range(“A2:C2”).NumberFormat = “#,##0.00”
.Range(“A2”).Value = 57
.Range(“B2”).Value = 84
‘calculate average value:
.Range(“C2”).Value = (.Range(“A2”).Value + .Range(“B2”).Value) / 2
‘use the AutoFit Method of the Range object to get the best fit for column width (columns A:C):
.Columns(“A:C”).AutoFit
‘enter the new worksheet name in range A4 – note that AutFit was used before this.
.Range(“A4”).Value = “Worksheet Name: “ & .Name
‘return the average score:
MsgBox “Average Score is: ” & .Range(“C2”).Value
End With
End With
End Sub
For live code of this example, click to download excel file.
Sub vbaCode4b()
‘writing the same vba code as above: do calculations in vba code with arithmetic operators, using variables – refer Image 5
‘declare variable of data type Double – the Double data type is used to store numericals that have a decimal component & permits 15 digits of accuracy.
Dim n As Double
With ThisWorkbook
Worksheets.Add After:=Worksheets(Worksheets.Count)
With .ActiveSheet
.Range(“A1”).Value = “Maths Score”
.Range(“B1”).Value = “English Score”
.Range(“C1”).Value = “Average Score”
.Range(“A2:C2”).NumberFormat = “#,##0.00”
.Range(“A2”).Value = 57
.Range(“B2”).Value = 84
‘store value in a variable:
n = (.Range(“A2”).Value + .Range(“B2”).Value) / 2
.Range(“C2”).Value = n
.Columns(“A:C”).AutoFit
.Range(“A4”).Value = “Worksheet Name: “ & .Name
MsgBox “Average Score is: ” & n
End With
End With
End Sub
Example 5: Assigning an Object to a Variable, using the Set Keyword – refer Image 3
For live code of this example, click to download excel file.
Sub vbaCode5()
‘Code is the same as in Example 1 (subs vbaCode1a & vbaCode1a) – now written by Assigning an Object to a Variable, using the Set Keyword
‘refer Image 3
‘declaring variables as Range type
Dim myRng1 As Range, myRng2 As Range, myRng3 As Range, myRng4 As Range
‘Assigning an Object to a Variable
Set myRng1 = ThisWorkbook.Worksheets(“Sheet1”).Range(“A1:C5”)
Set myRng2 = ThisWorkbook.Worksheets(“Sheet1”).Range(“A1:A5”)
Set myRng3 = ThisWorkbook.Worksheets(“Sheet1”).Range(“B1:B5”)
Set myRng4 = ThisWorkbook.Worksheets(“Sheet1”).Range(“C1:C5”)
‘using the object variable:
With myRng1
.Value = “hello”
With .Font
.Name = “Times New Roman”
.Size = 12
.Italic = True
End With
End With
With myRng2
.Font.Bold = True
‘set font color to yellow:
.Interior.ColorIndex = 6
End With
‘set font color to blue:
myRng3.Font.ColorIndex = 5
‘set font color to red:
myRng4.Font.ColorIndex = 3
End Sub