Want entry in column 16 to clear if value in column 12 is >= to 10
When entry is made into column 12 as target calls sub pledge following.

If target.column = 12 And target.Value >= 10 And IsNumeric(target.Value)
Then _
Call Pledge(target)

Public Sub Pledge(ByVal target As range)
Sheets("data").Select
target("column = 16").ClearContents
End Sub

Know I am missing something all entries in same row and will apply thru
complete sheet. Reason I believe (target) keeps column row relationship.

You can put this in the sheet code module by right clicking the sheet tab
then select View Code from the pop up menu and paste this into the window:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lr As Long
lr = Cells(Rows.Count, 12).End(xlUp).Row
If Not Intersect(Target, Range("L2:L" & lr) Is Nothing Then
If IsNumeric(Target.Value) And Target.Vaue >= 10 Then
Sheets("data").Range("P" & Target.Row).ClearContents
End If
End If
End Sub

This eliminates the need to call the othe macro. If the criteria is met, it
goes directly to the sheet and deletes clears the range in column 16 for the
same row as the target in column 12. Let me know if you get an error and
what the error message says.


"Curt" <(E-Mail Removed)> wrote in message
news:F276DC0D-5132-4C25-9ACE-(E-Mail Removed)...
> Want entry in column 16 to clear if value in column 12 is >= to 10
> When entry is made into column 12 as target calls sub pledge following.
>
> If target.column = 12 And target.Value >= 10 And IsNumeric(target.Value)
> Then _
> Call Pledge(target)
>
> Public Sub Pledge(ByVal target As range)
> Sheets("data").Select
> target("column = 16").ClearContents
> End Sub
>
> Know I am missing something all entries in same row and will apply thru
> complete sheet. Reason I believe (target) keeps column row relationship.

If Not Intersect(Target, Range("L2:L" & lr) Is Nothing Then
syntax error
tried putting underscore after Then no go
"JLGWhiz" wrote:

> You can put this in the sheet code module by right clicking the sheet tab
> then select View Code from the pop up menu and paste this into the window:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim lr As Long
> lr = Cells(Rows.Count, 12).End(xlUp).Row
> If Not Intersect(Target, Range("L2:L" & lr) Is Nothing Then
> If IsNumeric(Target.Value) And Target.Vaue >= 10 Then
> Sheets("data").Range("P" & Target.Row).ClearContents
> End If
> End If
> End Sub
>
> This eliminates the need to call the othe macro. If the criteria is met, it
> goes directly to the sheet and deletes clears the range in column 16 for the
> same row as the target in column 12. Let me know if you get an error and
> what the error message says.
>
>
> "Curt" <(E-Mail Removed)> wrote in message
> news:F276DC0D-5132-4C25-9ACE-(E-Mail Removed)...
> > Want entry in column 16 to clear if value in column 12 is >= to 10
> > When entry is made into column 12 as target calls sub pledge following.
> >
> > If target.column = 12 And target.Value >= 10 And IsNumeric(target.Value)
> > Then _
> > Call Pledge(target)
> >
> > Public Sub Pledge(ByVal target As range)
> > Sheets("data").Select
> > target("column = 16").ClearContents
> > End Sub
> >
> > Know I am missing something all entries in same row and will apply thru
> > complete sheet. Reason I believe (target) keeps column row relationship.
>
>
>

expected list seperator or ) also as error

"JLGWhiz" wrote:

> You can put this in the sheet code module by right clicking the sheet tab
> then select View Code from the pop up menu and paste this into the window:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim lr As Long
> lr = Cells(Rows.Count, 12).End(xlUp).Row
> If Not Intersect(Target, Range("L2:L" & lr) Is Nothing Then
> If IsNumeric(Target.Value) And Target.Vaue >= 10 Then
> Sheets("data").Range("P" & Target.Row).ClearContents
> End If
> End If
> End Sub
>
> This eliminates the need to call the othe macro. If the criteria is met, it
> goes directly to the sheet and deletes clears the range in column 16 for the
> same row as the target in column 12. Let me know if you get an error and
> what the error message says.
>
>
> "Curt" <(E-Mail Removed)> wrote in message
> news:F276DC0D-5132-4C25-9ACE-(E-Mail Removed)...
> > Want entry in column 16 to clear if value in column 12 is >= to 10
> > When entry is made into column 12 as target calls sub pledge following.
> >
> > If target.column = 12 And target.Value >= 10 And IsNumeric(target.Value)
> > Then _
> > Call Pledge(target)
> >
> > Public Sub Pledge(ByVal target As range)
> > Sheets("data").Select
> > target("column = 16").ClearContents
> > End Sub
> >
> > Know I am missing something all entries in same row and will apply thru
> > complete sheet. Reason I believe (target) keeps column row relationship.
>
>
>

If Not Intersect(Target, Range("L2:L" & lr) Is Nothing Then
did catch missing ) after Target, Range still didn't clear "p"

"JLGWhiz" wrote:

> You can put this in the sheet code module by right clicking the sheet tab
> then select View Code from the pop up menu and paste this into the window:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim lr As Long
> lr = Cells(Rows.Count, 12).End(xlUp).Row
> If Not Intersect(Target, Range("L2:L" & lr) Is Nothing Then
> If IsNumeric(Target.Value) And Target.Vaue >= 10 Then
> Sheets("data").Range("P" & Target.Row).ClearContents
> End If
> End If
> End Sub
>
> This eliminates the need to call the othe macro. If the criteria is met, it
> goes directly to the sheet and deletes clears the range in column 16 for the
> same row as the target in column 12. Let me know if you get an error and
> what the error message says.
>
>
> "Curt" <(E-Mail Removed)> wrote in message
> news:F276DC0D-5132-4C25-9ACE-(E-Mail Removed)...
> > Want entry in column 16 to clear if value in column 12 is >= to 10
> > When entry is made into column 12 as target calls sub pledge following.
> >
> > If target.column = 12 And target.Value >= 10 And IsNumeric(target.Value)
> > Then _
> > Call Pledge(target)
> >
> > Public Sub Pledge(ByVal target As range)
> > Sheets("data").Select
> > target("column = 16").ClearContents
> > End Sub
> >
> > Know I am missing something all entries in same row and will apply thru
> > complete sheet. Reason I believe (target) keeps column row relationship.
>
>
>

I had two typos in it. I tested it and it did clear the range in P.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim lr As Long
lr = Cells(Rows.Count, 12).End(xlUp).Row
If Not Intersect(Target, Range("L2:L" & lr)) Is Nothing Then
If IsNumeric(Target.Value) And Target.Value >= 10 Then
Sheets("data").Range("P" & Target.Row).ClearContents
End If
End If
End Sub


"Curt" <(E-Mail Removed)> wrote in message
news:8D6FFBAB-B622-4256-96EF-(E-Mail Removed)...
> If Not Intersect(Target, Range("L2:L" & lr) Is Nothing Then
> did catch missing ) after Target, Range still didn't clear "p"
>
> "JLGWhiz" wrote:
>
>> You can put this in the sheet code module by right clicking the sheet tab
>> then select View Code from the pop up menu and paste this into the
>> window:
>>
>> Private Sub Worksheet_Change(ByVal Target As Range)
>> Dim lr As Long
>> lr = Cells(Rows.Count, 12).End(xlUp).Row
>> If Not Intersect(Target, Range("L2:L" & lr) Is Nothing Then
>> If IsNumeric(Target.Value) And Target.Vaue >= 10 Then
>> Sheets("data").Range("P" & Target.Row).ClearContents
>> End If
>> End If
>> End Sub
>>
>> This eliminates the need to call the othe macro. If the criteria is met,
>> it
>> goes directly to the sheet and deletes clears the range in column 16 for
>> the
>> same row as the target in column 12. Let me know if you get an error and
>> what the error message says.
>>
>>
>> "Curt" <(E-Mail Removed)> wrote in message
>> news:F276DC0D-5132-4C25-9ACE-(E-Mail Removed)...
>> > Want entry in column 16 to clear if value in column 12 is >= to 10
>> > When entry is made into column 12 as target calls sub pledge following.
>> >
>> > If target.column = 12 And target.Value >= 10 And
>> > IsNumeric(target.Value)
>> > Then _
>> > Call Pledge(target)
>> >
>> > Public Sub Pledge(ByVal target As range)
>> > Sheets("data").Select
>> > target("column = 16").ClearContents
>> > End Sub
>> >
>> > Know I am missing something all entries in same row and will apply thru
>> > complete sheet. Reason I believe (target) keeps column row
>> > relationship.
>>
>>
>>

copyed it and worked fine Cells row count can be expanded I think will do it
if ok sheeet may have many rows used 65536 to cover all possible. If this
makes sense I 'm in your debt
If we didn't make typos we wouldn't be human
thanks

"JLGWhiz" wrote:

> I had two typos in it. I tested it and it did clear the range in P.
>
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim lr As Long
> lr = Cells(Rows.Count, 12).End(xlUp).Row
> If Not Intersect(Target, Range("L2:L" & lr)) Is Nothing Then
> If IsNumeric(Target.Value) And Target.Value >= 10 Then
> Sheets("data").Range("P" & Target.Row).ClearContents
> End If
> End If
> End Sub
>
>
> "Curt" <(E-Mail Removed)> wrote in message
> news:8D6FFBAB-B622-4256-96EF-(E-Mail Removed)...
> > If Not Intersect(Target, Range("L2:L" & lr) Is Nothing Then
> > did catch missing ) after Target, Range still didn't clear "p"
> >
> > "JLGWhiz" wrote:
> >
> >> You can put this in the sheet code module by right clicking the sheet tab
> >> then select View Code from the pop up menu and paste this into the
> >> window:
> >>
> >> Private Sub Worksheet_Change(ByVal Target As Range)
> >> Dim lr As Long
> >> lr = Cells(Rows.Count, 12).End(xlUp).Row
> >> If Not Intersect(Target, Range("L2:L" & lr) Is Nothing Then
> >> If IsNumeric(Target.Value) And Target.Vaue >= 10 Then
> >> Sheets("data").Range("P" & Target.Row).ClearContents
> >> End If
> >> End If
> >> End Sub
> >>
> >> This eliminates the need to call the othe macro. If the criteria is met,
> >> it
> >> goes directly to the sheet and deletes clears the range in column 16 for
> >> the
> >> same row as the target in column 12. Let me know if you get an error and
> >> what the error message says.
> >>
> >>
> >> "Curt" <(E-Mail Removed)> wrote in message
> >> news:F276DC0D-5132-4C25-9ACE-(E-Mail Removed)...
> >> > Want entry in column 16 to clear if value in column 12 is >= to 10
> >> > When entry is made into column 12 as target calls sub pledge following.
> >> >
> >> > If target.column = 12 And target.Value >= 10 And
> >> > IsNumeric(target.Value)
> >> > Then _
> >> > Call Pledge(target)
> >> >
> >> > Public Sub Pledge(ByVal target As range)
> >> > Sheets("data").Select
> >> > target("column = 16").ClearContents
> >> > End Sub
> >> >
> >> > Know I am missing something all entries in same row and will apply thru
> >> > complete sheet. Reason I believe (target) keeps column row
> >> > relationship.
> >>
> >>
> >>
>
>
>

I may have left something out My entries start in row 5 works ok with row
count set at 12. If i increase row count nbr stops working worksheet may have
up to 200 rows of entries.
lr = Cells(Rows.Count, 12).End(xlUp).Row
do we need to change intersect range
If Not Intersect(Target, Range("L2:L" & lr)) Is Nothing Then

I have this in a copy paste sub
Set rngPaste = wksSummary.Cells(65536, "A").End(xlUp).Offset(1, 0)
Been working on this program for 2 years now I am only retired one on Vet's
parade comittee who stepped up to do this. Without people like you us old
timers would never have been able to get it done. Sure made last year go good
had 33 entries on parade route in 10 minutes.
Really appreciate the help.

"JLGWhiz" wrote:

> I had two typos in it. I tested it and it did clear the range in P.
>
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim lr As Long
> lr = Cells(Rows.Count, 12).End(xlUp).Row
> If Not Intersect(Target, Range("L2:L" & lr)) Is Nothing Then
> If IsNumeric(Target.Value) And Target.Value >= 10 Then
> Sheets("data").Range("P" & Target.Row).ClearContents
> End If
> End If
> End Sub
>
>
> "Curt" <(E-Mail Removed)> wrote in message
> news:8D6FFBAB-B622-4256-96EF-(E-Mail Removed)...
> > If Not Intersect(Target, Range("L2:L" & lr) Is Nothing Then
> > did catch missing ) after Target, Range still didn't clear "p"
> >
> > "JLGWhiz" wrote:
> >
> >> You can put this in the sheet code module by right clicking the sheet tab
> >> then select View Code from the pop up menu and paste this into the
> >> window:
> >>
> >> Private Sub Worksheet_Change(ByVal Target As Range)
> >> Dim lr As Long
> >> lr = Cells(Rows.Count, 12).End(xlUp).Row
> >> If Not Intersect(Target, Range("L2:L" & lr) Is Nothing Then
> >> If IsNumeric(Target.Value) And Target.Vaue >= 10 Then
> >> Sheets("data").Range("P" & Target.Row).ClearContents
> >> End If
> >> End If
> >> End Sub
> >>
> >> This eliminates the need to call the othe macro. If the criteria is met,
> >> it
> >> goes directly to the sheet and deletes clears the range in column 16 for
> >> the
> >> same row as the target in column 12. Let me know if you get an error and
> >> what the error message says.
> >>
> >>
> >> "Curt" <(E-Mail Removed)> wrote in message
> >> news:F276DC0D-5132-4C25-9ACE-(E-Mail Removed)...
> >> > Want entry in column 16 to clear if value in column 12 is >= to 10
> >> > When entry is made into column 12 as target calls sub pledge following.
> >> >
> >> > If target.column = 12 And target.Value >= 10 And
> >> > IsNumeric(target.Value)
> >> > Then _
> >> > Call Pledge(target)
> >> >
> >> > Public Sub Pledge(ByVal target As range)
> >> > Sheets("data").Select
> >> > target("column = 16").ClearContents
> >> > End Sub
> >> >
> >> > Know I am missing something all entries in same row and will apply thru
> >> > complete sheet. Reason I believe (target) keeps column row
> >> > relationship.
> >>
> >>
> >>
>
>
>

Got it by makeing change in both lines
Thanks again

"JLGWhiz" wrote:

> I had two typos in it. I tested it and it did clear the range in P.
>
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim lr As Long
> lr = Cells(Rows.Count, 12).End(xlUp).Row
> If Not Intersect(Target, Range("L2:L" & lr)) Is Nothing Then
> If IsNumeric(Target.Value) And Target.Value >= 10 Then
> Sheets("data").Range("P" & Target.Row).ClearContents
> End If
> End If
> End Sub
>
>
> "Curt" <(E-Mail Removed)> wrote in message
> news:8D6FFBAB-B622-4256-96EF-(E-Mail Removed)...
> > If Not Intersect(Target, Range("L2:L" & lr) Is Nothing Then
> > did catch missing ) after Target, Range still didn't clear "p"
> >
> > "JLGWhiz" wrote:
> >
> >> You can put this in the sheet code module by right clicking the sheet tab
> >> then select View Code from the pop up menu and paste this into the
> >> window:
> >>
> >> Private Sub Worksheet_Change(ByVal Target As Range)
> >> Dim lr As Long
> >> lr = Cells(Rows.Count, 12).End(xlUp).Row
> >> If Not Intersect(Target, Range("L2:L" & lr) Is Nothing Then
> >> If IsNumeric(Target.Value) And Target.Vaue >= 10 Then
> >> Sheets("data").Range("P" & Target.Row).ClearContents
> >> End If
> >> End If
> >> End Sub
> >>
> >> This eliminates the need to call the othe macro. If the criteria is met,
> >> it
> >> goes directly to the sheet and deletes clears the range in column 16 for
> >> the
> >> same row as the target in column 12. Let me know if you get an error and
> >> what the error message says.
> >>
> >>
> >> "Curt" <(E-Mail Removed)> wrote in message
> >> news:F276DC0D-5132-4C25-9ACE-(E-Mail Removed)...
> >> > Want entry in column 16 to clear if value in column 12 is >= to 10
> >> > When entry is made into column 12 as target calls sub pledge following.
> >> >
> >> > If target.column = 12 And target.Value >= 10 And
> >> > IsNumeric(target.Value)
> >> > Then _
> >> > Call Pledge(target)
> >> >
> >> > Public Sub Pledge(ByVal target As range)
> >> > Sheets("data").Select
> >> > target("column = 16").ClearContents
> >> > End Sub
> >> >
> >> > Know I am missing something all entries in same row and will apply thru
> >> > complete sheet. Reason I believe (target) keeps column row
> >> > relationship.
> >>
> >>
> >>
>
>
>

This line:

lr = Cells(Rows.Count, 12).End(xlUp).Row

is to find the row number of the last cell with data in column 12 ("L"). By
using the variable "lr" as the row number, it allows the range to
automatically expand in code as the user adds to or eliminates rows of data.
To change the beginning cell of the range:

If Not Intersect(Target, Range("L2:L" & lr)) Is Nothing Then

Change the L2 in this line of code to L5. The code would then read:

If Not Intersect(Target, Range("L5:L" & lr)) Is Nothing Then

which in plain language means a cell somewhere from row five of column L to
the last row with data in column L.

Have a great parade. We old timers have to stick together.


"Curt" <(E-Mail Removed)> wrote in message
news:E892E63B-950D-4BE6-AE91-(E-Mail Removed)...
>I may have left something out My entries start in row 5 works ok with row
> count set at 12. If i increase row count nbr stops working worksheet may
> have
> up to 200 rows of entries.
> lr = Cells(Rows.Count, 12).End(xlUp).Row
> do we need to change intersect range
> If Not Intersect(Target, Range("L2:L" & lr)) Is Nothing Then
>
> I have this in a copy paste sub
> Set rngPaste = wksSummary.Cells(65536, "A").End(xlUp).Offset(1, 0)
> Been working on this program for 2 years now I am only retired one on
> Vet's
> parade comittee who stepped up to do this. Without people like you us old
> timers would never have been able to get it done. Sure made last year go
> good
> had 33 entries on parade route in 10 minutes.
> Really appreciate the help.
>
> "JLGWhiz" wrote:
>
>> I had two typos in it. I tested it and it did clear the range in P.
>>
>>
>> Private Sub Worksheet_Change(ByVal Target As Range)
>> Dim lr As Long
>> lr = Cells(Rows.Count, 12).End(xlUp).Row
>> If Not Intersect(Target, Range("L2:L" & lr)) Is Nothing Then
>> If IsNumeric(Target.Value) And Target.Value >= 10 Then
>> Sheets("data").Range("P" & Target.Row).ClearContents
>> End If
>> End If
>> End Sub
>>
>>
>> "Curt" <(E-Mail Removed)> wrote in message
>> news:8D6FFBAB-B622-4256-96EF-(E-Mail Removed)...
>> > If Not Intersect(Target, Range("L2:L" & lr) Is Nothing Then
>> > did catch missing ) after Target, Range still didn't clear "p"
>> >
>> > "JLGWhiz" wrote:
>> >
>> >> You can put this in the sheet code module by right clicking the sheet
>> >> tab
>> >> then select View Code from the pop up menu and paste this into the
>> >> window:
>> >>
>> >> Private Sub Worksheet_Change(ByVal Target As Range)
>> >> Dim lr As Long
>> >> lr = Cells(Rows.Count, 12).End(xlUp).Row
>> >> If Not Intersect(Target, Range("L2:L" & lr) Is Nothing Then
>> >> If IsNumeric(Target.Value) And Target.Vaue >= 10 Then
>> >> Sheets("data").Range("P" & Target.Row).ClearContents
>> >> End If
>> >> End If
>> >> End Sub
>> >>
>> >> This eliminates the need to call the othe macro. If the criteria is
>> >> met,
>> >> it
>> >> goes directly to the sheet and deletes clears the range in column 16
>> >> for
>> >> the
>> >> same row as the target in column 12. Let me know if you get an error
>> >> and
>> >> what the error message says.
>> >>
>> >>
>> >> "Curt" <(E-Mail Removed)> wrote in message
>> >> news:F276DC0D-5132-4C25-9ACE-(E-Mail Removed)...
>> >> > Want entry in column 16 to clear if value in column 12 is >= to 10
>> >> > When entry is made into column 12 as target calls sub pledge
>> >> > following.
>> >> >
>> >> > If target.column = 12 And target.Value >= 10 And
>> >> > IsNumeric(target.Value)
>> >> > Then _
>> >> > Call Pledge(target)
>> >> >
>> >> > Public Sub Pledge(ByVal target As range)
>> >> > Sheets("data").Select
>> >> > target("column = 16").ClearContents
>> >> > End Sub
>> >> >
>> >> > Know I am missing something all entries in same row and will apply
>> >> > thru
>> >> > complete sheet. Reason I believe (target) keeps column row
>> >> > relationship.
>> >>
>> >>
>> >>
>>
>>
>>