How to empty cell contents after another cell has been changed

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]
 
Last edited:

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A
[/th][th]
B
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2
[/td][td]student flew 3.2 hours day while [/td][td]
your method​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3
[/td][td]performing overwater hoist, helo-[/td][td]
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4
[/td][td]cast, and formation flight. CM[/td][td]
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5
[/td][td]performed all tasks to standard[/td][td]
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6
[/td][td]however needs to study the correct[/td][td]
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7
[/td][td]terminology.[/td][td]
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9
[/td][td]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.[/td][td]
using WrapText​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11
[/td][td]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.[/td][td]
Use ing {ALT}{ENTER} to place text on new line​
[/td][/tr]
[/table]
[Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]
 
Upvote 0
The sheet look a little different in reality than it does when uploaded :confused:
The uploading has somehow made the column much wider :eeek:

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 :)
 
Last edited:
Upvote 0
This is "Change_Event" is a Possible option for you current layout .

Code:
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
 
Upvote 0
Slight amendment require to delete column "B" data when alteration in "C" occurs.
Add line in red
Code:
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
 
Upvote 0
The sheet look a little different in reality than it does when uploaded :confused:
The uploading has somehow made the column much wider :eeek:

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!
 
Upvote 0
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.
 
Upvote 0
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".
 
Upvote 0
Slight amendment require to delete column "B" data when alteration in "C" occurs.
Add line in red
Code:
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?
 
Upvote 0
An alternative for you
Place code in sheet module
Code:
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

:warning: If text is added for a record without inserting a date then the initials of the previous record get wiped!
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,898
Messages
6,175,274
Members
452,628
Latest member
dd2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top