Hi,
Column B will have the criteria (y or n). If n is entered, the data in the
two cells adjacent and to the right of the current cell must have the
contents cleared. I can accomplish this for one row in Column B but I need
to have this occur for the other rows. This is what I have so far..

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng1 As Range
Dim rng2 As Range

Set rng1 = Range("b2")
Set rng2 = Range("c2:d2")

If LCase(rng1.Value) = "n" Then
rng2.clearcontents

End If
End Sub

Any help is appreciated. This is my first shot at VBA.

--
CK

Try the below code; but are sure you want this code within the
WorkSheet_Change event or as a separate macro..........


Dim lngRow As Long
Dim lngLastRow As Long

lngLastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
For lngRow = 2 To lngLastRow
If LCase(Range("B" & lngRow)) = "n" Then
Range("C" & lngRow & "" & lngRow).ClearContents
End If
Next

--
If this post helps click Yes
---------------
Jacob Skaria


"CK" wrote:

> Hi,
> Column B will have the criteria (y or n). If n is entered, the data in the
> two cells adjacent and to the right of the current cell must have the
> contents cleared. I can accomplish this for one row in Column B but I need
> to have this occur for the other rows. This is what I have so far..
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Dim rng1 As Range
> Dim rng2 As Range
>
> Set rng1 = Range("b2")
> Set rng2 = Range("c2:d2")
>
> If LCase(rng1.Value) = "n" Then
> rng2.clearcontents
>
> End If
> End Sub
>
> Any help is appreciated. This is my first shot at VBA.
>
> --
> CK

Your code worked, but the macro kept running so I had to break out of it.
Here's a more detailed explanation...
The only time I want the cells cleared is when someone enters the criteria
'n' and then I want the cells that are adjacent to be cleared immediately.
I'm creating a template and the customers will go down a list and respond "y
or n" to that list. If it is 'y', the cells adjacent will be highlighted in
yellow (I'm using conditional formatting for this); if it is 'n' the adaject
cell to the right must be cleared of content immediately.
Thanks for your help.
--
CK


"Jacob Skaria" wrote:

> Try the below code; but are sure you want this code within the
> WorkSheet_Change event or as a separate macro..........
>
>
> Dim lngRow As Long
> Dim lngLastRow As Long
>
> lngLastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
> For lngRow = 2 To lngLastRow
> If LCase(Range("B" & lngRow)) = "n" Then
> Range("C" & lngRow & "" & lngRow).ClearContents
> End If
> Next
>
> --
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "CK" wrote:
>
> > Hi,
> > Column B will have the criteria (y or n). If n is entered, the data in the
> > two cells adjacent and to the right of the current cell must have the
> > contents cleared. I can accomplish this for one row in Column B but I need
> > to have this occur for the other rows. This is what I have so far..
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> >
> > Dim rng1 As Range
> > Dim rng2 As Range
> >
> > Set rng1 = Range("b2")
> > Set rng2 = Range("c2:d2")
> >
> > If LCase(rng1.Value) = "n" Then
> > rng2.clearcontents
> >
> > End If
> > End Sub
> >
> > Any help is appreciated. This is my first shot at VBA.
> >
> > --
> > CK

by adjacent and to the right do you mean just the two cells immediately
right of the changed cell?

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 And Target.Column = 3 Then
If UCase(Target.Value) = "N" Then
Target.Resize(, 2).Offset(, 1).Clear
End If
End If
End Sub

"CK" <(E-Mail Removed)> wrote in message
news:93546051-1E4E-49A6-8C11-(E-Mail Removed)...
> Hi,
> Column B will have the criteria (y or n). If n is entered, the data in
> the
> two cells adjacent and to the right of the current cell must have the
> contents cleared. I can accomplish this for one row in Column B but I
> need
> to have this occur for the other rows. This is what I have so far..
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Dim rng1 As Range
> Dim rng2 As Range
>
> Set rng1 = Range("b2")
> Set rng2 = Range("c2:d2")
>
> If LCase(rng1.Value) = "n" Then
> rng2.clearcontents
>
> End If
> End Sub
>
> Any help is appreciated. This is my first shot at VBA.
>
> --
> CK

Yes, the two cells immediately to the right of the changed cell. I tried
your code and the cells were not cleared when I typed an "n". I changed the
UCase to LCase and the "N" to "n"
--
CK


"Patrick Molloy" wrote:

> by adjacent and to the right do you mean just the two cells immediately
> right of the changed cell?
>
> Option Explicit
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Count = 1 And Target.Column = 3 Then
> If UCase(Target.Value) = "N" Then
> Target.Resize(, 2).Offset(, 1).Clear
> End If
> End If
> End Sub
>
> "CK" <(E-Mail Removed)> wrote in message
> news:93546051-1E4E-49A6-8C11-(E-Mail Removed)...
> > Hi,
> > Column B will have the criteria (y or n). If n is entered, the data in
> > the
> > two cells adjacent and to the right of the current cell must have the
> > contents cleared. I can accomplish this for one row in Column B but I
> > need
> > to have this occur for the other rows. This is what I have so far..
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> >
> > Dim rng1 As Range
> > Dim rng2 As Range
> >
> > Set rng1 = Range("b2")
> > Set rng2 = Range("c2:d2")
> >
> > If LCase(rng1.Value) = "n" Then
> > rng2.clearcontents
> >
> > End If
> > End Sub
> >
> > Any help is appreciated. This is my first shot at VBA.
> >
> > --
> > CK
>

Filter the event by the range you want..(For col C)

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
If Not Application.Intersect(Target, Range("C:C")) Is Nothing Then

'place you code

End If
Application.EnableEvents = True
End Sub
--
If this post helps click Yes
---------------
Jacob Skaria


"CK" wrote:

> Your code worked, but the macro kept running so I had to break out of it.
> Here's a more detailed explanation...
> The only time I want the cells cleared is when someone enters the criteria
> 'n' and then I want the cells that are adjacent to be cleared immediately.
> I'm creating a template and the customers will go down a list and respond "y
> or n" to that list. If it is 'y', the cells adjacent will be highlighted in
> yellow (I'm using conditional formatting for this); if it is 'n' the adaject
> cell to the right must be cleared of content immediately.
> Thanks for your help.
> --
> CK
>
>
> "Jacob Skaria" wrote:
>
> > Try the below code; but are sure you want this code within the
> > WorkSheet_Change event or as a separate macro..........
> >
> >
> > Dim lngRow As Long
> > Dim lngLastRow As Long
> >
> > lngLastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
> > For lngRow = 2 To lngLastRow
> > If LCase(Range("B" & lngRow)) = "n" Then
> > Range("C" & lngRow & "" & lngRow).ClearContents
> > End If
> > Next
> >
> > --
> > If this post helps click Yes
> > ---------------
> > Jacob Skaria
> >
> >
> > "CK" wrote:
> >
> > > Hi,
> > > Column B will have the criteria (y or n). If n is entered, the data in the
> > > two cells adjacent and to the right of the current cell must have the
> > > contents cleared. I can accomplish this for one row in Column B but I need
> > > to have this occur for the other rows. This is what I have so far..
> > >
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > >
> > > Dim rng1 As Range
> > > Dim rng2 As Range
> > >
> > > Set rng1 = Range("b2")
> > > Set rng2 = Range("c2:d2")
> > >
> > > If LCase(rng1.Value) = "n" Then
> > > rng2.clearcontents
> > >
> > > End If
> > > End Sub
> > >
> > > Any help is appreciated. This is my first shot at VBA.
> > >
> > > --
> > > CK

Jacob,
I'm totally lost now. I do not want to filter the column. As the customer
changes an entry in col B to an 'n' I need to have the two adjacent cells
cleared of content immediately. They might select another cell in that
column and enter an 'n' and then I would like the adjacent cells relative to
that acitve cell changed.
Thanks, again.
--
CK


"Jacob Skaria" wrote:

> Filter the event by the range you want..(For col C)
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Application.EnableEvents = False
> If Not Application.Intersect(Target, Range("C:C")) Is Nothing Then
>
> 'place you code
>
> End If
> Application.EnableEvents = True
> End Sub
> --
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "CK" wrote:
>
> > Your code worked, but the macro kept running so I had to break out of it.
> > Here's a more detailed explanation...
> > The only time I want the cells cleared is when someone enters the criteria
> > 'n' and then I want the cells that are adjacent to be cleared immediately.
> > I'm creating a template and the customers will go down a list and respond "y
> > or n" to that list. If it is 'y', the cells adjacent will be highlighted in
> > yellow (I'm using conditional formatting for this); if it is 'n' the adaject
> > cell to the right must be cleared of content immediately.
> > Thanks for your help.
> > --
> > CK
> >
> >
> > "Jacob Skaria" wrote:
> >
> > > Try the below code; but are sure you want this code within the
> > > WorkSheet_Change event or as a separate macro..........
> > >
> > >
> > > Dim lngRow As Long
> > > Dim lngLastRow As Long
> > >
> > > lngLastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
> > > For lngRow = 2 To lngLastRow
> > > If LCase(Range("B" & lngRow)) = "n" Then
> > > Range("C" & lngRow & "" & lngRow).ClearContents
> > > End If
> > > Next
> > >
> > > --
> > > If this post helps click Yes
> > > ---------------
> > > Jacob Skaria
> > >
> > >
> > > "CK" wrote:
> > >
> > > > Hi,
> > > > Column B will have the criteria (y or n). If n is entered, the data in the
> > > > two cells adjacent and to the right of the current cell must have the
> > > > contents cleared. I can accomplish this for one row in Column B but I need
> > > > to have this occur for the other rows. This is what I have so far..
> > > >
> > > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > >
> > > > Dim rng1 As Range
> > > > Dim rng2 As Range
> > > >
> > > > Set rng1 = Range("b2")
> > > > Set rng2 = Range("c2:d2")
> > > >
> > > > If LCase(rng1.Value) = "n" Then
> > > > rng2.clearcontents
> > > >
> > > > End If
> > > > End Sub
> > > >
> > > > Any help is appreciated. This is my first shot at VBA.
> > > >
> > > > --
> > > > CK

you put the code in the sheet's code page? It wouldn't matter if you type N
or n the UCASE would have left N as is and changed n to N and compared ok


"CK" <(E-Mail Removed)> wrote in message
news:71ABB488-F584-48F1-914D-(E-Mail Removed)...
> Yes, the two cells immediately to the right of the changed cell. I tried
> your code and the cells were not cleared when I typed an "n". I changed
> the
> UCase to LCase and the "N" to "n"
> --
> CK
>
>
> "Patrick Molloy" wrote:
>
>> by adjacent and to the right do you mean just the two cells immediately
>> right of the changed cell?
>>
>> Option Explicit
>> Private Sub Worksheet_Change(ByVal Target As Range)
>> If Target.Count = 1 And Target.Column = 3 Then
>> If UCase(Target.Value) = "N" Then
>> Target.Resize(, 2).Offset(, 1).Clear
>> End If
>> End If
>> End Sub
>>
>> "CK" <(E-Mail Removed)> wrote in message
>> news:93546051-1E4E-49A6-8C11-(E-Mail Removed)...
>> > Hi,
>> > Column B will have the criteria (y or n). If n is entered, the data in
>> > the
>> > two cells adjacent and to the right of the current cell must have the
>> > contents cleared. I can accomplish this for one row in Column B but I
>> > need
>> > to have this occur for the other rows. This is what I have so far..
>> >
>> > Private Sub Worksheet_Change(ByVal Target As Range)
>> >
>> > Dim rng1 As Range
>> > Dim rng2 As Range
>> >
>> > Set rng1 = Range("b2")
>> > Set rng2 = Range("c2:d2")
>> >
>> > If LCase(rng1.Value) = "n" Then
>> > rng2.clearcontents
>> >
>> > End If
>> > End Sub
>> >
>> > Any help is appreciated. This is my first shot at VBA.
>> >
>> > --
>> > CK
>>

Yes, I put the code on the sheet page. To make sure that I'm not missing
something. Is the code that you provided the only code that needs to be on
the sheet's code page?

Also, on the line Target.Resize(, 2).Offset(, 1).Clear should that be
ClearContents? I even changed that but it still didn't work. My data starts
in cell B2 (y or n info) and cell c2 and d2 need to be cleared of content if
the reposne is 'n'. If customer changes the entry in B24 to n then the cells
C24 and D24 need to be cleared.

Thanks for your help. Maybe this is over my head.

--
CK


"Patrick Molloy" wrote:

> you put the code in the sheet's code page? It wouldn't matter if you type N
> or n the UCASE would have left N as is and changed n to N and compared ok
>
>
> "CK" <(E-Mail Removed)> wrote in message
> news:71ABB488-F584-48F1-914D-(E-Mail Removed)...
> > Yes, the two cells immediately to the right of the changed cell. I tried
> > your code and the cells were not cleared when I typed an "n". I changed
> > the
> > UCase to LCase and the "N" to "n"
> > --
> > CK
> >
> >
> > "Patrick Molloy" wrote:
> >
> >> by adjacent and to the right do you mean just the two cells immediately
> >> right of the changed cell?
> >>
> >> Option Explicit
> >> Private Sub Worksheet_Change(ByVal Target As Range)
> >> If Target.Count = 1 And Target.Column = 3 Then
> >> If UCase(Target.Value) = "N" Then
> >> Target.Resize(, 2).Offset(, 1).Clear
> >> End If
> >> End If
> >> End Sub
> >>
> >> "CK" <(E-Mail Removed)> wrote in message
> >> news:93546051-1E4E-49A6-8C11-(E-Mail Removed)...
> >> > Hi,
> >> > Column B will have the criteria (y or n). If n is entered, the data in
> >> > the
> >> > two cells adjacent and to the right of the current cell must have the
> >> > contents cleared. I can accomplish this for one row in Column B but I
> >> > need
> >> > to have this occur for the other rows. This is what I have so far..
> >> >
> >> > Private Sub Worksheet_Change(ByVal Target As Range)
> >> >
> >> > Dim rng1 As Range
> >> > Dim rng2 As Range
> >> >
> >> > Set rng1 = Range("b2")
> >> > Set rng2 = Range("c2:d2")
> >> >
> >> > If LCase(rng1.Value) = "n" Then
> >> > rng2.clearcontents
> >> >
> >> > End If
> >> > End Sub
> >> >
> >> > Any help is appreciated. This is my first shot at VBA.
> >> >
> >> > --
> >> > CK
> >>

so sorry

my code was almost good enough but

change 3 to 2 here...

If Target.Count = 1 And Target.Column = 2 Then

I was checking col 3 which is C not 2 for B

many apologies


"CK" <(E-Mail Removed)> wrote in message
news:BBB3E89D-48BD-4B80-8386-(E-Mail Removed)...
> Yes, I put the code on the sheet page. To make sure that I'm not missing
> something. Is the code that you provided the only code that needs to be
> on
> the sheet's code page?
>
> Also, on the line Target.Resize(, 2).Offset(, 1).Clear should that be
> ClearContents? I even changed that but it still didn't work. My data
> starts
> in cell B2 (y or n info) and cell c2 and d2 need to be cleared of content
> if
> the reposne is 'n'. If customer changes the entry in B24 to n then the
> cells
> C24 and D24 need to be cleared.
>
> Thanks for your help. Maybe this is over my head.
>
> --
> CK
>
>
> "Patrick Molloy" wrote:
>
>> you put the code in the sheet's code page? It wouldn't matter if you
>> type N
>> or n the UCASE would have left N as is and changed n to N and compared ok
>>
>>
>> "CK" <(E-Mail Removed)> wrote in message
>> news:71ABB488-F584-48F1-914D-(E-Mail Removed)...
>> > Yes, the two cells immediately to the right of the changed cell. I
>> > tried
>> > your code and the cells were not cleared when I typed an "n". I
>> > changed
>> > the
>> > UCase to LCase and the "N" to "n"
>> > --
>> > CK
>> >
>> >
>> > "Patrick Molloy" wrote:
>> >
>> >> by adjacent and to the right do you mean just the two cells
>> >> immediately
>> >> right of the changed cell?
>> >>
>> >> Option Explicit
>> >> Private Sub Worksheet_Change(ByVal Target As Range)
>> >> If Target.Count = 1 And Target.Column = 3 Then
>> >> If UCase(Target.Value) = "N" Then
>> >> Target.Resize(, 2).Offset(, 1).Clear
>> >> End If
>> >> End If
>> >> End Sub
>> >>
>> >> "CK" <(E-Mail Removed)> wrote in message
>> >> news:93546051-1E4E-49A6-8C11-(E-Mail Removed)...
>> >> > Hi,
>> >> > Column B will have the criteria (y or n). If n is entered, the data
>> >> > in
>> >> > the
>> >> > two cells adjacent and to the right of the current cell must have
>> >> > the
>> >> > contents cleared. I can accomplish this for one row in Column B but
>> >> > I
>> >> > need
>> >> > to have this occur for the other rows. This is what I have so far..
>> >> >
>> >> > Private Sub Worksheet_Change(ByVal Target As Range)
>> >> >
>> >> > Dim rng1 As Range
>> >> > Dim rng2 As Range
>> >> >
>> >> > Set rng1 = Range("b2")
>> >> > Set rng2 = Range("c2:d2")
>> >> >
>> >> > If LCase(rng1.Value) = "n" Then
>> >> > rng2.clearcontents
>> >> >
>> >> > End If
>> >> > End Sub
>> >> >
>> >> > Any help is appreciated. This is my first shot at VBA.
>> >> >
>> >> > --
>> >> > CK
>> >>