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:

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

Posted

in

,

by

Tags:


Related Posts

Comments

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Discover more from Jitendra Zaa

Subscribe now to keep reading and get access to the full archive.

Continue Reading