{"id":2453,"date":"2011-09-28T15:43:49","date_gmt":"2011-09-28T10:13:49","guid":{"rendered":"http:\/\/JitendraZaa.com\/blog\/?p=2453"},"modified":"2011-09-28T15:43:49","modified_gmt":"2011-09-28T10:13:49","slug":"microsot-excel-change-content-of-alternate-cell-on-change-of-the-cell-value-using-macros","status":"publish","type":"post","link":"https:\/\/www.jitendrazaa.com\/blog\/microsoft\/microsot-excel-change-content-of-alternate-cell-on-change-of-the-cell-value-using-macros\/","title":{"rendered":"microsot Excel – Change content of alternate cell on change of the cell value using macros"},"content":{"rendered":"

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”<\/strong>.<\/p>\n

Requirement:<\/strong><\/p>\n

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:<\/p>\n

\"Change<\/a>
Change content of alternate cell on change of the cell value using macros<\/figcaption><\/figure>\n

Right click on “Sheet 1” tab at bottom and select the “View code”.<\/p>\n

\"View<\/a>
View Code in Microsoft Excel<\/figcaption><\/figure>\n

Source editor will open, write down the below source code in sheet 1:<\/p>\n

\nSub Worksheet_Change(ByVal Target As Range)\n    Dim WatchRange As Range\n    Dim IntersectRange As Range\n\n    Set WatchRange = Range("B2:B20")\n    Set IntersectRange = Intersect(Target, WatchRange)\n    If IntersectRange Is Nothing Then\n        'Do Nothing Spectacular\n    Else\n        If ActiveCell.Value = "Fruit" Then\n           response = MsgBox("Press on YES for Mango and NO for Banana", vbYesNo, "Select Fruit")\n           If response = vbYes Then\n                ActiveCell.Offset(0, 1).Value = "Mango"\n            Else\n                ActiveCell.Offset(0, 1).Value = "Banana"\n           End If\n        End If\n    End If\nEnd Sub\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
Using VBA script to change the content of alternate column on the basis of value changed in other column of excel sheet<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"advanced_seo_description":"","jetpack_seo_html_title":"","jetpack_seo_noindex":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"jz_research_post":"","_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_post_was_ever_published":false},"categories":[5,17],"tags":[93],"class_list":["post-2453","post","type-post","status-publish","format-standard","hentry","category-microsoft","category-tips","tag-excel"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack-related-posts":[{"id":3556,"url":"https:\/\/www.jitendrazaa.com\/blog\/java\/create-excel-file-in-java-using-apache-poi-library\/","url_meta":{"origin":2453,"position":0},"title":"Create Excel File in Java using Apache POI Library","author":"Jitendra","date":"October 15, 2013","format":false,"excerpt":"Recently, I came across requirement to create ExcelSheet from thin Java Client used by Salesforce. So, I though to share my experience on Creating Excel Sheet in Java. As we know that Java is product of Oracle and Excel is product of Microsoft. Off-course, There will be no standard functionality\u2026","rel":"","context":"In "JAVA"","block_context":{"text":"JAVA","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/java\/"},"img":{"alt_text":"Create Excel File in Java","src":"https:\/\/i0.wp.com\/jitendrazaa.com\/blog\/wp-content\/uploads\/2013\/10\/Create-Excel-File-in-Java-300x166.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":1619,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/sqlserverintegrationservices\/merge-two-data-in-etl-project-of-ssis\/","url_meta":{"origin":2453,"position":1},"title":"Merge Two Data in ETL project of SSIS","author":"Jitendra","date":"March 10, 2011","format":false,"excerpt":"Example of Merging two data in ETL project of SSIS","rel":"","context":"In "SSIS"","block_context":{"text":"SSIS","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/sqlserverintegrationservices\/"},"img":{"alt_text":"Merge Two Data in ETL project of SSIS","src":"https:\/\/i0.wp.com\/jitendrazaa.com\/blog\/wp-content\/uploads\/2011\/03\/Merge-Two-Data-in-ETL-project-of-SSIS.jpg?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":2299,"url":"https:\/\/www.jitendrazaa.com\/blog\/microsoft\/net\/tutorial-read-and-export-excel-file-in-asp-net-using-c\/","url_meta":{"origin":2453,"position":2},"title":"Tutorial – Read and export excel file in ASP.Net  using C#","author":"Jitendra","date":"July 29, 2011","format":false,"excerpt":"Tutorial - Read and export excel file in ASP.Net using C#","rel":"","context":"In "ASP.NET"","block_context":{"text":"ASP.NET","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/microsoft\/net\/"},"img":{"alt_text":"Read And Export Excel in ASP.Net","src":"https:\/\/i0.wp.com\/jitendrazaa.com\/blog\/wp-content\/uploads\/2011\/07\/Read-And-Export-Excel-in-ASP.Net_.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":467,"url":"https:\/\/www.jitendrazaa.com\/blog\/microsoft\/net\/telerik-radschedular-tips-and-tricks\/","url_meta":{"origin":2453,"position":3},"title":"Telerik RadSchedular Tips and Tricks","author":"Jitendra","date":"July 1, 2010","format":false,"excerpt":"Change the style of the Today Cell in Month View and Bind Extra Column \/ Add Argument in RadSchedular","rel":"","context":"In "ASP.NET"","block_context":{"text":"ASP.NET","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/microsoft\/net\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":3218,"url":"https:\/\/www.jitendrazaa.com\/blog\/salesforce\/salesforce-interview-question-part-15\/","url_meta":{"origin":2453,"position":4},"title":"Salesforce Interview Question \u2013 Part 15","author":"Jitendra","date":"May 24, 2013","format":false,"excerpt":"141 : User Wants to set the starting day in Calendar as \"Monday\" instead of \"Sunday\". How to get it done? Ans : Change the user locale to \"English ( United Kingdom ) \" in Personal information or User record. 142 : Why CSS is not working in PDF created\u2026","rel":"","context":"In "Salesforce"","block_context":{"text":"Salesforce","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/salesforce\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1178,"url":"https:\/\/www.jitendrazaa.com\/blog\/microsoft\/net\/datagrid-gridview-datalist-and-repeater-control-in-asp-net\/","url_meta":{"origin":2453,"position":5},"title":"DataGrid, GridView, DataList and Repeater Control in ASP.net","author":"Jitendra","date":"October 1, 2010","format":false,"excerpt":"DataGrid, GridView, DataList and Repeater Control in ASP.net","rel":"","context":"In "ASP.NET"","block_context":{"text":"ASP.NET","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/microsoft\/net\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/posts\/2453","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/comments?post=2453"}],"version-history":[{"count":0,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/posts\/2453\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/media?parent=2453"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/categories?post=2453"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/tags?post=2453"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}