Change event when existing text is changed

arcaidius

Board Regular
Joined
Dec 4, 2018
Messages
63
Trying to perform a change event when value is changed within a range to highlight the cell that changed, and insert date and user into C1 and D1.
I have been searching about it for a few hours now everything I have found only works when the cell is blank. If the cell has existing values it is highlighted as soon as I select the cell. Tracking changes on the review Tab does not work because it has tables. Can anyone please help? Code I have so far is:

VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("B:B,C:C,D:D")) Is Nothing Then
      If Target <> "" Then Target.Interior.ColorIndex = 36
   End If
End Sub
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,510
Office Version
  1. 365
Platform
  1. Windows
How exactly is the text changed?
Is it changed manually, or is it a formula or link?

If it is changed manually, you will want to use a "Worksheet_Change" event instead, which triggers when some range is manually changed.
 

arcaidius

Board Regular
Joined
Dec 4, 2018
Messages
63
in a table... not sure if that matters.

VBA Code:
Range("Table1[[Licensor]:[M Max]]")
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,510
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Which column does this occur in?
Column B?
 

arcaidius

Board Regular
Joined
Dec 4, 2018
Messages
63
Oh man I'm an idiot! It was on worksheet but as "Selection change" not "Change".

Do you know how to insert the user in a cell when this happens?
 

arcaidius

Board Regular
Joined
Dec 4, 2018
Messages
63
Thank you Sir. Everything works perfect! here is final code: (on worksheet - Change)

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("Table1[[Licensor]:[M Max]]")) Is Nothing Then
      If Target <> "" Then Target.Interior.ColorIndex = 36
      Range("D1") = Now
      Range("C1").Value = Environ("Username")
   End If
End Sub
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,510
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
Glad you got everything working the way you want!
 

Watch MrExcel Video

Forum statistics

Threads
1,132,685
Messages
5,654,748
Members
418,149
Latest member
amamiche67

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
Top