microsot Excel – Change content of alternate cell on change of the cell value using macros

Microsoft excel is very powerful tool to work on the related data. This time i needed a unique functionality in excel sheet and wanted to share with you all. So to create the macro enabled excel sheet, while saving file in file types, select the micro enabled workbook. In Microsoft Excel 2010 the extension of such type of file is “xlsm”.

Requirement:

on changing of auto complete column, a message box should appear and after clicking on button, the adjacent cell value should get changed as shown in below image:

Right click on “Sheet 1” tab at bottom and select the “View code”.

Source editor will open, write down the below source code in sheet 1:

Sub Worksheet_Change(ByVal Target As Range)
    Dim WatchRange As Range
    Dim IntersectRange As Range

    Set WatchRange = Range("B2:B20")
    Set IntersectRange = Intersect(Target, WatchRange)
    If IntersectRange Is Nothing Then
        'Do Nothing Spectacular
    Else
        If ActiveCell.Value = "Fruit" Then
           response = MsgBox("Press on YES for Mango and NO for Banana", vbYesNo, "Select Fruit")
           If response = vbYes Then
                ActiveCell.Offset(0, 1).Value = "Mango"
            Else
                ActiveCell.Offset(0, 1).Value = "Banana"
           End If
        End If
    End If
End Sub