Hello,
I have two columns A and B. Column A is "CustomerStatus" and Column B is "AccountStatus". I would like to have some VBA code that automatically enters the word "Inactive" in B2 (AccountStatus) if A2 (CustomerStatus) also = "Inactive." Preferably not in a button or macro that I run, just as soon as any value in Column A changes to "Inactive".
Any help?
Hi, b624333,
you may like to use the Worksheet_Change-event behind the sheet. Right-click on the worksheet Tab, choose Vioew code and paste in the procedure into the code window, close the VBE. Remeber that there may only be one event of teh given name behind the sheet:
Ciao,
Please Login or Register to view this content.
Holger
Use Code-Tags for showing your code: [code] Your Code here [/code]
Please mark your question Solved if there has been offered a solution that works fine for you
This works great.
One more question: How would I change the above code to do the same thing if I just moved Column A to D and Column B to E?
Hi, b624333,
you would have to change the Column number accordingly. 1 stands for Column A, so you would need to put in 4 for D. The output will always be one cell to the right of the cell checked, if you want a different offset you would have to change the number in there accordingly (positive numbers go to the right in Column or down in rows, negative ones left or up).
HTH,
Holger
Even more complicated now actually. Column A now has a VLOOKUP formula in it. Is there any way to still enter "Inactive" in Column B when Column A = "Inactive" from the VLOOKUP? Using the code that you already wrote?
This is what I meant originally, but I thought I was explaining the problem in a more simplified way.
Last edited by b624333; 06-25-2014 at 10:58 PM. Reason: spelling errors
Hi, b624333,
the Chenge-event will only get fired once any value is entered or edited in a cell on the range/sheet this code is behind. If you have a formula you should consider to monitor the Lookup-range for any chnages (using Woirksheet_Change-event), use the Worksheet_Calculate-event and try to narrow the range down to just the formula range you want to monitor (you would need a loop there I would guess) or maybe even consider to use the Worksheet_Activate-event behind the sheet with the formulas depending on the number of actualisations for the lookup range (this would use a loop as well).
I was mislead by that and didn´t ask for a clarification on a direct entry/an edit or a formula in the cell and came up with the wrong suggestion, I´m sorry about that. Anyhow you could apply a formula to the resulting column range which would leave the cell empty or showing a wanted expression if the corresponding cell doeswn´t show "inactive".This is what I meant originally, but I thought I was explaining the problem in a more simplified way.
Ciao,
Holger
It was my fault. I should have known it would have made a difference. I wasn't thinking.
Could you explain what you mean when you say "monitor the Lookup range"? I know it is hard to explain without specific data ranges. I am VERY new to VBA. I can post my workbook if that's helpful.
Hi, b624333,
if I´m correct the VLookup formula consists of the item to search for, the range where to search, the column which should be returned and a Boolean indicating how to proceed if no match is found. So you would have the information inside the formula as the second set of infoirmation.
Maybe you should attach a workbook that reflects your situation whithout confidential material for us to have a look at - for my part I will only be able to look at it in a couple of hours.
And you should consider to revert the thread not to be Solved via Thread-Tools.
Ciao,
Holger
version2.0.xlsm
Attached, is my current file. The affected area (where the VBA code would be needed) is on Sheet "Accounts" in columns D and E. Again, I would need AccountStatus (Columnn E) to automatically say "Inactive" when the CustomerStatus (Column D) results in "Inactive" from the VLOOKUP function in its cell.
Hi, b624333,
why not have a formula in Column E instead of your request of VBA to solve this?
Formula:
Please Login or Register to view this content.
Ciao,
Holger
That is a possibility. However, it's not as dynamic as I would like it to be. For example, when a Customer Status is not "Inactive" (i.e. Active), I still have to choose if the Account Status belonging to the Customer is Active OR Inactive. once I choose one of those options, the IF formula is deleted from the cell.
That being said, I would love to have a VBA code.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks