Clear Cell Contents based on Criteria of another cell

[CK]

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.

 

[Jacob Skaria]

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

 

[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.

 

[Patrick Molloy]

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]

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"

 

[Jacob Skaria]

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

 

[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.

 

[Patrick Molloy]

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

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]

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.

 

[Patrick Molloy]

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]

Thank you, thank you and thank you ... it is a beautiful sight.