Help with a code

I have these code and it works find: but i also want to use it for a different culumn within the same sheet. how do i add Column D with a different target? meaning "S" for Study and "B" for books and so on...

and maybe adding a Msg box to it that will only allow the Letters i specify..
thank you in advance..

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = "" Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("c6:c50")) Is Nothing Then
If UCase(Target) = "C" Then
Target = "Contribution"
Else
If UCase(Target) = "D" Then
Target = "Deposits"
Else
If UCase(Target) = "N" Then Target = "N/A"
End If
End If
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = "" Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("c6:c50")) Is Nothing Then
If UCase(Target) = "C" Then
Target = "Contribution"
Else
If UCase(Target) = "D" Then
Target = "Deposits"
Else
If UCase(Target) = "N" Then Target = "N/A"
End If
End If
Else
If Not Intersect(Target, Range("D6:D50")) Is Nothing Then
If UCase(Target) = "S" Then
Target = "Study"
Else
If UCase(Target) = "B" Then
Target = "Books"
Else
If UCase(Target) = "N" Then Target = "N/A"
End If
End If
End If
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Joe@excel" <Joeexcel.1yxwly_1132732801.2265@excelforum-nospam.com> wrote in
message news:Joeexcel.1yxwly_1132732801.2265@excelforum-nospam.com...
>
> I have these code and it works find: but i also want to use it for a
> different culumn within the same sheet. how do i add Column D with a
> different target? meaning "S" for Study and "B" for books and so
> on...
>
> and maybe adding a Msg box to it that will only allow the Letters i
> specify..
> thank you in advance..
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target = "" Then Exit Sub
> If Target.Count > 1 Then Exit Sub
> If Not Intersect(Target, Range("c6:c50")) Is Nothing Then
> If UCase(Target) = "C" Then
> Target = "Contribution"
> Else
> If UCase(Target) = "D" Then
> Target = "Deposits"
> Else
> If UCase(Target) = "N" Then Target = "N/A"
> End If
> End If
> End If
> End Sub
>
>
> --
> Joe@excel
> ------------------------------------------------------------------------
> Joe@excel's Profile:
http://www.excelforum.com/member.php...o&userid=29016
> View this thread: http://www.excelforum.com/showthread...hreadid=487536
>

A typo here, and can be simplified with

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = "" Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("c6:c50")) Is Nothing Then
If UCase(Target) = "C" Then
Target = "Contribution"
ElseIf UCase(Target) = "D" Then
Target = "Deposits"
ElseIf UCase(Target) = "N" Then
Target = "N/A"
End If
ElseIf Not Intersect(Target, Range("D6:D50")) Is Nothing Then
If UCase(Target) = "S" Then
Target = "Study"
ElseIf UCase(Target) = "B" Then
Target = "Books"
ElseIf UCase(Target) = "N" Then
Target = "N/A"
End If
End If
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:uGSSvrC8FHA.604@TK2MSFTNGP10.phx.gbl...
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target = "" Then Exit Sub
> If Target.Count > 1 Then Exit Sub
> If Not Intersect(Target, Range("c6:c50")) Is Nothing Then
> If UCase(Target) = "C" Then
> Target = "Contribution"
> Else
> If UCase(Target) = "D" Then
> Target = "Deposits"
> Else
> If UCase(Target) = "N" Then Target = "N/A"
> End If
> End If
> Else
> If Not Intersect(Target, Range("D6:D50")) Is Nothing Then
> If UCase(Target) = "S" Then
> Target = "Study"
> Else
> If UCase(Target) = "B" Then
> Target = "Books"
> Else
> If UCase(Target) = "N" Then Target = "N/A"
> End If
> End If
> End If
> End Sub
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Joe@excel" <Joeexcel.1yxwly_1132732801.2265@excelforum-nospam.com> wrote
in
> message news:Joeexcel.1yxwly_1132732801.2265@excelforum-nospam.com...
> >
> > I have these code and it works find: but i also want to use it for a
> > different culumn within the same sheet. how do i add Column D with a
> > different target? meaning "S" for Study and "B" for books and so
> > on...
> >
> > and maybe adding a Msg box to it that will only allow the Letters i
> > specify..
> > thank you in advance..
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > If Target = "" Then Exit Sub
> > If Target.Count > 1 Then Exit Sub
> > If Not Intersect(Target, Range("c6:c50")) Is Nothing Then
> > If UCase(Target) = "C" Then
> > Target = "Contribution"
> > Else
> > If UCase(Target) = "D" Then
> > Target = "Deposits"
> > Else
> > If UCase(Target) = "N" Then Target = "N/A"
> > End If
> > End If
> > End If
> > End Sub
> >
> >
> > --
> > Joe@excel
> > ------------------------------------------------------------------------
> > Joe@excel's Profile:
> http://www.excelforum.com/member.php...o&userid=29016
> > View this thread:
http://www.excelforum.com/showthread...hreadid=487536
> >
>
>

Another idea using select case

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column = 3 Then
Select Case UCase(Target)
Case "D": x = "Dee"
Case "E": x = "Eeee"

Case Else: x = Target
End Select
Target = x
End If
'---------
If Target.Column = 4 Then
Select Case UCase(Target)
Case "D": x = "Dee"
Case "E": x = "Eeee"

Case Else: x = Target
End Select
Target = x
End If
Application.EnableEvents = True
End Sub


--
Don Guillett
SalesAid Software
donaldb@281.com
"Joe@excel" <Joeexcel.1yxwly_1132732801.2265@excelforum-nospam.com> wrote in
message news:Joeexcel.1yxwly_1132732801.2265@excelforum-nospam.com...
>
> I have these code and it works find: but i also want to use it for a
> different culumn within the same sheet. how do i add Column D with a
> different target? meaning "S" for Study and "B" for books and so
> on...
>
> and maybe adding a Msg box to it that will only allow the Letters i
> specify..
> thank you in advance..
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target = "" Then Exit Sub
> If Target.Count > 1 Then Exit Sub
> If Not Intersect(Target, Range("c6:c50")) Is Nothing Then
> If UCase(Target) = "C" Then
> Target = "Contribution"
> Else
> If UCase(Target) = "D" Then
> Target = "Deposits"
> Else
> If UCase(Target) = "N" Then Target = "N/A"
> End If
> End If
> End If
> End Sub
>
>
> --
> Joe@excel
> ------------------------------------------------------------------------
> Joe@excel's Profile:
> http://www.excelforum.com/member.php...o&userid=29016
> View this thread: http://www.excelforum.com/showthread...hreadid=487536
>

One more!

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim tempVal As Variant

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("c6:c50,d6:d50")) Is Nothing Then Exit Sub

On Error GoTo errHandler:

Application.EnableEvents = False

tempVal = ""
Select Case Target.Column
Case Is = Me.Range("C1").Column
Select Case LCase(Target.Value)
Case "c": tempVal = "Contribution"
Case "d": tempVal = "Deposit"
Case "n": tempVal = "N/A"
End Select
Case Is = Me.Range("d1").Column
Select Case LCase(Target.Value)
Case "s": tempVal = "Study"
Case "b": tempVal = "Books"
End Select
End Select

If tempVal = "" Then
'do nothing
Else
Target.Value = tempVal
End If

errHandler:
Application.EnableEvents = True
End Sub



"Joe@excel" wrote:
>
> I have these code and it works find: but i also want to use it for a
> different culumn within the same sheet. how do i add Column D with a
> different target? meaning "S" for Study and "B" for books and so
> on...
>
> and maybe adding a Msg box to it that will only allow the Letters i
> specify..
> thank you in advance..
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target = "" Then Exit Sub
> If Target.Count > 1 Then Exit Sub
> If Not Intersect(Target, Range("c6:c50")) Is Nothing Then
> If UCase(Target) = "C" Then
> Target = "Contribution"
> Else
> If UCase(Target) = "D" Then
> Target = "Deposits"
> Else
> If UCase(Target) = "N" Then Target = "N/A"
> End If
> End If
> End If
> End Sub
>
> --
> Joe@excel
> ------------------------------------------------------------------------
> Joe@excel's Profile: http://www.excelforum.com/member.php...o&userid=29016
> View this thread: http://www.excelforum.com/showthread...hreadid=487536

--

Dave Peterson

thank you all for the great help.... it works fine..