change of data

[roger@]

i have a formula in column L(it consist formula and the result can be nothing or true or false)
example
column L
true
false
false

now what i want is if the data in column L has true then i want the notification of the same what i want in the notification is student name which is in column B and the data in column L
EXAMPLE
Raj true(notification)

or if the data in column L has false then i want the notification of the same what i want in the notification is student name which is in column B and the data in column L
rani false(notification)
i want notification of the same
the vba should run continously and notifying me the same until i close the file
if i got notification of one student and meanwhile another student met the condition then i want the notification of the same

[HansV]

On which cell or cells does the formula in column L depend?

[roger@]

it depend on column C and F

[roger@]

but we have to concentrate on column L
in my file there are lots of formula so plz concentrate on column L and our target is column L

[roger@]

and plz let me know that if we know our target is column L then y we require column L depend on which cell
Assume Column L depends on xyz cell but we have to concentrate on column L only

[roger@]

and one more thing if possible then i want the notification along with student name and column L data and with current pc time
example
RAJ TRUE 10:30pm or am depend on pc time

[HansV]

Excel only tells us which cells are changed by the user. It doesn't tell us which cells that contain a formula are changed. So there is no way for us to react to a change in column L, we can only react to a change in the cells that the formula in column L depends on.

In other words, what you ask is impossible (again).

[roger@]

fine no problem
I can do one more thing with this problem
My column L contains the formula and the result we got in column L will be blank or TRUE or False

First My column L will be blank the formula will return to the blank result
and column L depends on B or C or xyz when B C or xyz met the condition then column L will return to TRUE OR FALSE
So now what i want that when columb L turns from Blank result to true or false then notify me
here blank means no data in L columns(the formula result is nothing)

[HansV]

Here is code for the worksheet module. You will notice that it has a strange side effect: when you change a cell in column B or C causing column L to change from blank to not-blank and press Enter or Tab, the active cell won't change.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim CurVal As Variant
    Dim OldVal As Variant
    Dim NewVal As Variant
    Dim r As Long
    If Target.Count > 1 Then Exit Sub
    ' The formula in column L depends on column B and C in the same row
    If Not Intersect(Range("B:B,C:C"), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        CurVal = Target.Value
        r = Target.Row
        NewVal = Range("L" & r).Value
        Application.Undo
        OldVal = Range("L" & r).Value
        Target.Value = CurVal
        If OldVal = "" And NewVal <> "" Then
            MsgBox "Column L changed to TRUE/FALSE in row " & r, vbInformation
        End If
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

[roger@]

i will not change column B and C
Column B and C consist a formula
so the result of the formula will change automatically bcoz column B and column C depend on another column
So i think this code will work
what u think hansv Sir now this code will work according to me or still there will be strange side effect plz guide sir

[HansV]

You have to change Range("B:B,C:C") to the range of cells that YOU (the user) will edit/modify/change and that will directly or indirectly determine the result of the formulas in column L.
It won't work with Range("B:B,C:C") if columns B and C contain formulas.

[roger@]

Plz have a look in the sample file i have mentioned all details in it
if any doubts plz let me know

[HansV]

See the attached version.

sample.xlsm

[roger@]

This code is perfect, this is what i wanted
but i have an issue with this vba code
if it happens one by one then this code is working but if it happens with many it is not notifying the same
if column K is changing with more than one cells then this vba code is not working
if column k is changing with only one cells then this vba code is working so plz have a look and do needful Sir

[HansV]

Here you go:

sample.xlsm

[roger@]

This code is perfect, this is what i wanted
but i have a doubt
it shows the notification perfect but meanwhile while showing the notification if the data of column K changes (another cells new one )then that notification it will show how
example
K5 AND K6 data changed it is showing notification(perfect) but meanwhile after 1 second K7 data changes then notification for K7 will be after pressing ok of the k5 and k6 notification am i right?

[HansV]

Yes.

[roger@]

Thnx Sir