Entering a date into a cell based on the date when another cell updates

[Pwtyang]

Hi

I got the macro below from the forum that enables me to do just as I've mentioned above, however is there any way that it can do the date update based on specific text in the in the target cell that is being populated by using an index, so its not a change to the target cell as such? The target cell is pulling through text from an array where the value that is constantly changing based on the stage a calculation is at so it could be one of the following;

submitted
in progress
queried
completed

I want the date to populate in the next cell when the text is equal to "completed". Please forgive me if I'm not explaining this to well as I'm a total VBA virgin and not that hot in excel either


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("a:a")) Is Nothing Then
Target.Offset(0, 1) = Date
End If
End Sub

[6StringJazzer]

Welcome to the Forum!

Since it is your first post I will go ahead and answer your question but be aware that the Forum has several rules, including tagging code (see my signature for an example and an admonition at the bottom of this post). I suggest your read over the rules (also linked on the top menu of every page).

Please Login or Register  to view this content.

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

[Pwtyang]

Hi thanks for getting back and I apologise for not reading the rules first. Just wondered how I could get it now to do both this below then your code as well in the same worksheet, but with your code looking at a different cell. Its just I have two cells where I want the date to populate, one based on where a specific was last updated which the first code covers then the other based on your code where it looks for the explicit text in another cell. Sorry I should of said I needed both to work in the same worksheet.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[6StringJazzer]

Here are two ways to do the same thing (I have not actually tested it but I have done this sort of thing many times). Because you want to take the same action in either case, you can combine the two conditions. It is not necessary; some people might think the original is better for readability and some think that this is better for economy of expression. Also, strictly speaking the parentheses I added are not necessary because And takes precedence over Or, but I always use them in compound logical expressions to avoid any possibility of confusion.

Please Login or Register  to view this content.

Since you only care about what column the changed cell is in, a shorter way to write this is:

Please Login or Register  to view this content.

[Pwtyang]

Thanks again, I'll give that a test later today and let you know how it goes