Action on cell C1 when cell B1 changes with function

[belodelokelo]

Dear All,

I have this function on cell B1:

=FILTER('RAW'C:C,'RAW'T:T=A1)

Basically, if on A1 I write something it checks a database on "RAW" sheet and returns the relevant value to B1.

Then what I want for the below code is auto-detect any change on column B and do something on column C. I used for simplicity the .Value = "Works!" function since it's important for me now to have the explained functionality.

Briefly if I type on A then it filters B based on the table and does something else on C. So with one enter to do both.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer
Dim Xrg As Range

Set Xrg = Range("B:B")
Set WorkRng = Intersect(Application.ActiveSheet.Range("B:B"), Target)
xOffsetColumn = 2
If Not WorkRng Is Nothing Then
Application.EnableEvents = False
For Each Rng In WorkRng

If Not Intersect(Xrg, Range("B:B")) Is Nothing Then
Rng.Offset(0, xOffsetColumn).Value = "Works!"
Else
Rng.Offset(0, xOffsetColumn).Value = "No"
End If


Next
Application.EnableEvents = True
End If
End Sub

 

[Fluff]

To put the text of the comment into the cell you could use

                Target.Offset(, 2).Value = wsRAW.Range("C" & r).CommentThreaded.Text

 

[Joe4]

2. The question was that you suggested a way to do this that you said equites you to ask MVPs etc. That way of doing it is faster vs commenting?

What I actually want to achieve here is to mention people on the shared file. This is not possible based on what I have read with VBA. Is this the case? If it was possible it would replace the copy/paste part of the code.

Take a look at the reply Fluff just made. Apparently, returning that might have been easier than I thought (it appears it is just a matter of getting the right syntax!).

 

[belodelokelo]

Take a look at the reply Fluff just made. Apparently, returning that might have been easier than I thought (it appears it is just a matter of getting the right syntax!).

That's clear thank you.

Quick question based on this part:

' Exit if multiple cells updated at once
If Target.CountLarge > 1 Then Exit Sub

What would happen if 10 people worked on this Excel at once?

 

[Joe4]

That line of code has nothing to with how many people are working on it. It has to do with the number of cells being updated at once.
Basically, the way a number of cells would be updated in one instant was if a Copy/Paste was doing, pasting to multiple cells at once.

 

[belodelokelo]

That line of code has nothing to with how many people are working on it. It has to do with the number of cells being updated at once.
Basically, the way a number of cells would be updated in one instant was if a Copy/Paste was doing, pasting to multiple cells at once.

Joe4 hello again and thanks for this last comment. I would like to ask if it's possible to track a cell value change if it has a formula in it.

Right now the input is on column E. What if on Column E we had a formula and the output value of E was connected to something else? I understand that right now it doesn't work because the macro reads the actual text value of the cell which is always the same (since it's the formula). Can we read the output of the formula and based on that output track changes and make the rest of the Macro work?

 

[Joe4]

Joe4 hello again and thanks for this last comment. I would like to ask if it's possible to track a cell value change if it has a formula in it.

I think we have already answered this question in this thread. As I said MANY times, NO, at least not directly.
You cannot track a formula that changes value. But you can track the underlying values that feed into the formula, if those are updated manually.

 

[belodelokelo]

I think we have already answered this question in this thread. As I said MANY times, NO, at least not directly.
You cannot track a formula that changes value. But you can track the underlying values that feed into the formula, if those are updated manually.

Any smart way to actually achieve this indirectly? Maybe copying the output value of the formula from E to another cell and track the change based on the other cell somehow?

 

[Joe4]

Any smart way to actually achieve this indirectly? Maybe copying the output value of the formula from E to another cell and track the change based on the other cell somehow?

I have already tried to explain different methods of doing it indirectly already in this thread, so there is really no point in my repeating myself.
It has been a few weeks, so perhaps you have forgotten. I suggest going back and re-reading all the replies in this thread.

 

[belodelokelo]

I have already tried to explain different methods of doing it indirectly already in this thread, so there is really no point in my repeating myself.
It has been a few weeks, so perhaps you have forgotten. I suggest going back and re-reading all the replies in this thread.

Actually, no real solution has been suggested except setting the process for the cell that is initially changing manually which is obvious.

 

[belodelokelo]

View attachment 88885

'***************act = ...  '*********THIS IS THE PART WE NEED HELP ON HERE!

I will reach out to some MVP friends to see if they know how to fix that one part.

Thank you for all the help so far! I would like to ask if you figured out essentially how to pull comments on VBA together with mention tags (@Name). I only found this that comes close Excel VBA UserForm Tag Property.

Any other ideas much appreciated!