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>

 

[Yongle]

Before replying to your question...

Do you really want your worksheet to look like that?

Format the column as alignment WrapText and enter all the text into one cell - Excel splits the text onto different lines within the cell automatically
Or when user wants to put text onto next line within the cell use {ALT}{ENTER} whilst entering text in the cell

Excel 2016 (Windows) 32 bit

	A​	B​
1​
2​	student flew 3.2 hours day while	your method​
3​	performing overwater hoist, helo-	​
4​	cast, and formation flight. CM	​
5​	performed all tasks to standard	​
6​	however needs to study the correct	​
7​	terminology.	​
8​
9​	student flew 3.2 hours day while performing overwater hoist, helo-cast, and formation flight. CM performed all tasks to standard however needs to study the correct terminology.	using WrapText​
10​
11​	student flew 3.2 hours day while performing overwater hoist, helo-cast, and formation flight. CM performed all tasks to standard however needs to study the correct terminology.	Use ing {ALT}{ENTER} to place text on new line​

Sheet: Sheet1

 

[Yongle]

The sheet look a little different in reality than it does when uploaded
The uploading has somehow made the column much wider

in essence
- WraPText fits as much text on each line as determined by your column width. So if the column is very narrow you get the more lines
- {ALT}{ENTER} allows user to put in line breaks wherever required

I usually format the text as WrapText and use {Alt}{Enter} when a new sentence is started or for bullet points etcwithin the same cell - then it looks exactly how I want

 

[MickG]

This is "Change_Event" is a Possible option for you current layout .

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("C:C").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
        [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
              [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

 

[MickG]

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

 

[dsheard2015]

The sheet look a little different in reality than it does when uploaded
The uploading has somehow made the column much wider

in essence
- WraPText fits as much text on each line as determined by your column width. So if the column is very narrow you get the more lines
- {ALT}{ENTER} allows user to put in line breaks wherever required

I usually format the text as WrapText and use {Alt}{Enter} when a new sentence is started or for bullet points etcwithin the same cell - then it looks exactly how I want

Yongle,
Thanks for your suggestion but unfortunately, I cannot go that route. I should have mentioned earlier but because this is an official form I need to keep the rows formatted the same with only 1 line of text per row. Thanks again!

 

[dsheard2015]

Thanks MickG for your reply. I won't be able to plug this code into the worksheet for a few hours when I'm back at my desk but just taking a look at your code, I already have conditional formatting in place that will change the color from white to yellow so can those lines of code be removed? Thanks again.

 

[MickG]

The conditional formatting "Yellow" is not the same "Yellow" used by the code. I think it would get rather messy/difficult trying use conditional formatting for one section of the requirement then trying to overwrite it with another piece of code to cater for alterations in column "C".
I should try the code without the C/F.
NB:- The code relies on a Blank row between the last words in column "C" and a new date in column "A".

 

[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,

I just installed your code into my worksheet and nothing happened at all. I removed all the cond. formatting on the page and even left an empty row as you suggested.

Any thoughts?

 

[Yongle]

An alternative for you
Place code in sheet module

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim C  As Range, B As Range
    Set C = Range("C7")             'cell above the data
    With Target
        If .Column = 3 And .Row > C.Row Then
            If .Offset(, -2) > "" Then
                Set B = .Offset(, -1)
            Else
                Set B = .Offset(, -2).End(xlUp).Offset(, 1)
            End If
            If Not B Is Nothing Then B.ClearContents
        End If
    End With
End Sub

You worksheet is badly desined for what you are trying to do

The date in column A is used to determine the first line of a record

If text is added for a record without inserting a date then the initials of the previous record get wiped!