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!

	Column A-Date	Column B-Initials	Column C-Comments
8	23 Jan 2019	DAH	Student flew 2.5 hours day while
9			performing overwater hoist training.
10
11	24 Jan 2019	DAH	student flew 3.2 hours day while
12			performing overwater hoist, helo-
13			cast, and formation flight. CM
14			performed all tasks to standard
15			however needs to study the correct
16			terminology.
17
18
19
20
21

<tbody>
</tbody>

 

[MARK858]

Not if the code is running to completion now (you can test this by putting a messagebox immediately above End Sub).

 

[dsheard2015]

MARK858,

The message appears so it seems to be running to completion. Any idea why a change in the top row would not delete the initials?

 

[dsheard2015]

Slight amendment require to delete column "B" data when alteration in "C" occurs.
Add line in red

If Not Intersect(R, Target) Is Nothing Then
                 Cells(R(1).Row, "B").Interior.Color = vbYellow
                [COLOR=#FF0000][B] Cells(R(1).Row, "B").Value = ""
[/B][/COLOR]              End If

MickG,

your code is working great unless a change is made in the top row of the comment, then only the color changes to yellow and the initials ARE NOT deleted. Any ideas on how to get it working correctly?

Thanks!

 

[MickG]

Try this:-

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] Rw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, R [COLOR="Navy"]As[/COLOR] Range
Rw = Target.Row
Application.EnableEvents = False
[COLOR="Navy"]Set[/COLOR] Rng = Range("C2", Range("C" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants)
[COLOR="Navy"]If[/COLOR] Target.Count = 1 [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]If[/COLOR] Target.Column = 1 And Target.Offset(-1, 2) <> "" [COLOR="Navy"]Then[/COLOR] Target = ""
        [COLOR="Navy"]If[/COLOR] Target.Column = 2 And Target <> "" [COLOR="Navy"]Then[/COLOR]
            Target.Interior.Color = xlNone
        [COLOR="Navy"]ElseIf[/COLOR] Cells(Target.Row, "A") <> "" And Cells(Target.Row, "C") <> "" [COLOR="Navy"]Then[/COLOR]
            Cells(Target.Row, "B").Interior.Color = vbYellow
             Cells(Target.Row, "B").Value = ""
        [COLOR="Navy"]ElseIf[/COLOR] Target.Column = 3 [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] R [COLOR="Navy"]In[/COLOR] Rng.Areas
               [COLOR="Navy"]If[/COLOR] Not Intersect(R, Target) [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
                 Cells(R(1).Row, "B").Interior.Color = vbYellow
                 Cells(R(1).Row, "B").Value = ""
              [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]Next[/COLOR] R
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]ElseIf[/COLOR] Target.Count > 1 [COLOR="Navy"]Then[/COLOR]
    Target = ""
[COLOR="Navy"]End[/COLOR] If
Application.EnableEvents = True
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[dsheard2015]

Try this:-

Private [COLOR=Navy]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR=Navy]As[/COLOR] Range)
[COLOR=Navy]Dim[/COLOR] Rw [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range, R [COLOR=Navy]As[/COLOR] Range
Rw = Target.Row
Application.EnableEvents = False
[COLOR=Navy]Set[/COLOR] Rng = Range("C2", Range("C" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants)
[COLOR=Navy]If[/COLOR] Target.Count = 1 [COLOR=Navy]Then[/COLOR]
    [COLOR=Navy]If[/COLOR] Target.Column = 1 And Target.Offset(-1, 2) <> "" [COLOR=Navy]Then[/COLOR] Target = ""
        [COLOR=Navy]If[/COLOR] Target.Column = 2 And Target <> "" [COLOR=Navy]Then[/COLOR]
            Target.Interior.Color = xlNone
        [COLOR=Navy]ElseIf[/COLOR] Cells(Target.Row, "A") <> "" And Cells(Target.Row, "C") <> "" [COLOR=Navy]Then[/COLOR]
            Cells(Target.Row, "B").Interior.Color = vbYellow
             Cells(Target.Row, "B").Value = ""
        [COLOR=Navy]ElseIf[/COLOR] Target.Column = 3 [COLOR=Navy]Then[/COLOR]
            [COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] R [COLOR=Navy]In[/COLOR] Rng.Areas
               [COLOR=Navy]If[/COLOR] Not Intersect(R, Target) [COLOR=Navy]Is[/COLOR] Nothing [COLOR=Navy]Then[/COLOR]
                 Cells(R(1).Row, "B").Interior.Color = vbYellow
                 Cells(R(1).Row, "B").Value = ""
              [COLOR=Navy]End[/COLOR] If
            [COLOR=Navy]Next[/COLOR] R
        [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]ElseIf[/COLOR] Target.Count > 1 [COLOR=Navy]Then[/COLOR]
    Target = ""
[COLOR=Navy]End[/COLOR] If
Application.EnableEvents = True
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

Hello MickG,

what does "C2" in line 5 refer to? Is it supposed to be the first row of comments? If so, that would actually be C8. I just wanted to be sure before I made the change.

 

[dsheard2015]

Thanks for all your help MickG and to everyone else! I changed C2 to C8 and the code appears to be working perfectly!

Have a great day and thanks again!

 

[MickG]

You're welcome