February 13, 2014

Active Cell

Working with active cell in Microsoft Excel
Working with active cell using VBA: The ActiveCell property in Microsoft Excel returns a Range object that represents the cell that is active. You can apply any of the properties or methods of a Range object to the active cell, as in the following macros.

This first method is intuitive.

Sub SetActive()
ActiveCell.Font.Bold = True
ActiveCell.Font.Size = 12
ActiveCell.Value = 4900
End Sub

The second macro below adheres to exact VBA coding.

Sub SetActive2()
With Range(“A2”)
.Font.Bold = True
.Font.Size = 14
.Font.ColorIndex = 3
.Value = 8900
End With
End Sub

The Excel training video demonstrates the concept clearly.

Implementing the macro:
1.Click on Tools in the menu bar, select Macro and from the listed options select Visual Basic Editor
2.In the VB editor window click on insert and select Module
3.The first line of code defines the mandatory macro name and the last line sub end is added automatically
4.The second line activates the worksheet 1 or sheet1
5.The third line selects the cell A2 and makes it ready for data input
6.The fourth line of macro code arranges the data to be made bold once it is entered
7.The fifth line defines the size of the font

8.The second last line enters ‘4900’ into cell A2
As you can see the thinking through of each step is important and the writing of the code comes with some practice and you learn the syntax automatically. It may appear a little difficult in the beginning. Just keep practicing thoughtfully and you’ll master VBA.

Further reading:
Active cell