How to empty cell contents after another cell has been changed

[dsheard2015]

I have a worksheet comprised of columns A, B and C. Column A is the date, column B is the student initials (which are entered AFTER they read the comments), and column C is the instructor comments. The comments in column C for each date are normally at least 2 rows however, they can be as long as 30 rows. The data within this worksheet is entered in the range A8:C53.

Using the example below, when the date is entered in A11 and a comment is entered in C11:C16, cell B11 will turn yellow, because of conditional formatting. After the student reads the comment and enters their initials in B11 then the cell will turn white, again because of conditional formatting. The issue I have is when/if the instructor needs to change part of the comment in C14 (for example), I need the initials in B11 to clear which in turn will again cause the cell to turn yellow.

I would like to have this done using vba and I'm guessing it would need to be a worksheet change event however, I just don't know how to write it. Any help is greatly appreciated!

[TABLE="class: grid, width: 700, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]Column A-Date[/TD]
[TD]Column B-Initials[/TD]
[TD]Column C-Comments[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]23 Jan 2019[/TD]
[TD]DAH[/TD]
[TD]Student flew 2.5 hours day while[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD]performing overwater hoist training.[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]24 Jan 2019[/TD]
[TD]DAH[/TD]
[TD]student flew 3.2 hours day while[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD]performing overwater hoist, helo-[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD]cast, and formation flight. CM[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD]performed all tasks to standard[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD]however needs to study the correct[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD]terminology.[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[MickG]

Have you placed the code in the Worksheet Module, not ordinary Module.
To load code:- Right click sheet tab, select "View Code", vbwindow appears , paste code in this window.

 

[MickG]

Here's an Example file:-
https://app.box.com/s/oeum2dz49mvlfdza7pluqbgqez2xnhjm

 

[Yongle]

did you test code in post#10?

 

[dsheard2015]

Hello MickG,

Yes, the code was placed in the worksheet module.

 

[dsheard2015]

Hello Yongle,

Thanks for your reply. I placed your code into the worksheet module and nothing happened.

 

[MARK858]

@dsheard2015
Run code below and then see if it makes a difference to the other codes running.

Sub Re_Enable()
Application.EnableEvents = True
End Sub

 

[dsheard2015]

Hello Mark858,

Very interesting! I added your code and the other code from MickG works great! My vba knowledge isn't the best so I don't understand how your code makes this all work. application.enableevents = true is already included in the original code.

hmmm. Anyways, thank you for your input.

 

[MARK858]

You should run it as a separate macro, the reason it makes it run is because at some point the code didn't complete (possibly because of an error) and so it never reached the Application.EnableEvents = True so it was still at the False state.

 

[dsheard2015]

Ok, I understand that.

Here is an update. When I change the comments in any row besides the top row of each comment the code works correctly, It will delete the initials for that comment and turn the cell yellow. however, when I change the top row of any comment, the cell turns yellow but it does not delete the initials.

 

[dsheard2015]

You should run it as a separate macro, the reason it makes it run is because at some point the code didn't complete (possibly because of an error) and so it never reached the Application.EnableEvents = True so it was still at the False state.

Do you think the reason for the initials not being deleted is related to this "possible error" with the enable events?

I really do appreciate your help!