dsheard2015
Board Regular
- Joined
- May 10, 2016
- Messages
- 134
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]
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]
Last edited: