Excel VBA Example

Excel 2007 Excel 2003 Macros Excel 2007 Macros Excel 2003 Excel 2010




During a classroom session one of the students asked this question:
What formula can I use in MS Excel to do the following
  • For example in cell B2 I have a value 12
  • I will input value 6 in cell C2
  • Cell B2 will become 6 (Value in Cell B2 - Value in Cell C2)
  • Cell C2 should become blank
  • If I again put a value 5 in cell C2, the same process should happen.
  • Cell B2 will become 1 (6 - 5)
  • Cell C2 should become blank
The problem cannot be solved with an Excel formula or function. You'll have to work with Excel VBA.The complete code (macros) is given below followed by a video:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If IsNumeric(Range("C2").Value) Then
Range("B2").Value = Range("B2").Value - Range("C2").Value
Range("C2").Value = ""
End If
End Sub

Watch the training video below to learn how to solve this problem using Excel VBA:

Excel 2007 Excel 2003 Macros Excel 2007 Macros Excel 2003 Excel 2010