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

Using VBA script to change the content of alternate column on the basis of value changed in other column of excel sheet

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:

Change content of alternate cell on change of the cell value using macros
Change content of alternate cell on change of the cell value using macros

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

View Code in Microsoft Excel
View Code in Microsoft Excel

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

Related posts

Leave a Reply

Your email address will not be published. Required fields are marked *