VB Code applied to entire column

michelliep · Jul 10th 2012

I am using this VB code to make cell P4 clear it's contents when 100, 150, 1500 is entered into cell D4. How do I apply this action to the entire columns of D4 and P4?

Code

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range) 
     
    If Target.Cells.Count > 1 Then Exit Sub
     
    If Target.Address = "$D$4" Then
        With Target 
            Select Case .Value 
            Case 100, 150, 1500: Range("P4").ClearContents 
Case Else: 'do nothing
            End Select
        End With
    End If
     
End Sub

Display More

**cytop** · Jul 10th 2012

Re: VB Code applied to entire column

Any particular reason this post is exactly the same as one by another member...?

http://www.ozgrid.com/forum/showthread.php?t=167348

Same IP address ... and the name can be construed to be the same as well.

You have replies in the other thread, what was wrong with them?

michelliep · Jul 10th 2012

Re: VB Code applied to entire column

What is wrong with asking a question? I thought that was the purpose of this website? I started a new thread because that is what I was instructed to do instead of reply with a new question to my old thread.

**cytop** · Jul 10th 2012

Re: VB Code applied to entire column

What I said at the time...

Quote

if there are any other requirements in addition to your original query and the need to apply it to an entire column please state them now

I was simply pointing out that changing the requirements can affect the solution... in this case I never mentioned anything about starting a new thread.

But now that you have, I was curious as to why you posted under a new user name.

Richie(UK) · Jul 10th 2012

Re: VB Code applied to entire column

Whichever username you decide to go with please remember to use code tags when posting code to the forum. I've added them for you this time.

Richie(UK) · Jul 10th 2012

Re: VB Code applied to entire column

Is this the sort of thing that you are looking for?

Code

Private Sub Worksheet_Change(ByVal Target As Range)
     
    If Target.Cells.Count > 1 Then Exit Sub
     
    If Target.Column = 4 Then
        With Target
            Select Case .Value
                Case 100, 150, 1500: Range("P" & Target.Row).ClearContents
                Case Else: 'do nothing
            End Select
        End With
    End If
     
End Sub

Display More

VB Code applied to entire column

Participate now!

Share