Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear Cell Contents based on Criteria of another cell
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear Cell Contents based on Criteria of another cell
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 & ":D" & 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear Cell Contents based on Criteria of another cell
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 & ":D" & 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear Cell Contents based on Criteria of another cell
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear Cell Contents based on Criteria of another cell
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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear Cell Contents based on Criteria of another cell
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 & ":D" & 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear Cell Contents based on Criteria of another cell
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 & ":D" & 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear Cell Contents based on Criteria of another cell
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" wrote in message ... 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" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear Cell Contents based on Criteria of another cell
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" wrote in message ... 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" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear Cell Contents based on Criteria of another cell
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear Cell Contents based on Criteria of another cell
Thank you, thank you and thank you ... it is a beautiful sight.
-- CK "Patrick Molloy" wrote: 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Display contents of cell based on criteria | Excel Worksheet Functions | |||
Replacing Path/filename and criteria in MS Query based on cell contents using VBA | Excel Programming | |||
Finding cell contents based on certain criteria | Excel Discussion (Misc queries) | |||
Finding cell contents based on certain criteria | Excel Discussion (Misc queries) | |||
Delete/clear a cell based on another cells contents | Excel Programming |