Is there a way to enter a letter in a letter in a cell and once the
Enter key is struck, the letter will change to become a symbol that is
located in another cell? More specifically:

1) I have multiple ranges where I usually enter an "x". The ranges
are A1:C10 and A20:C30.

2) There is a symbol in Z1.

When I enter the "x" in multiples places within my ranges, I want the
"x" (or any other letter that might be entered) to instantly change to
the symbol when I stike the Enter key. Is this possible? Thanks for
any help you may offer.

Michael

Right click on sheet tab, view code, paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:C10")) Is Nothing And _
Intersect(Target, Range("A20:C30")) Is Nothing Then Exit Sub
Application.EnableEvents = False
On Error Resume Next
Target.Value = Range("Z1").Value
Application.EnableEvents = True
End Sub
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Michael Lanier" wrote:

Is there a way to enter a letter in a letter in a cell and once the
Enter key is struck, the letter will change to become a symbol that is
located in another cell? More specifically:

1) I have multiple ranges where I usually enter an "x". The ranges
are A1:C10 and A20:C30.

2) There is a symbol in Z1.

When I enter the "x" in multiples places within my ranges, I want the
"x" (or any other letter that might be entered) to instantly change to
the symbol when I stike the Enter key. Is this possible? Thanks for
any help you may offer.

Michael

Thanks Luke. Unfortunately the following debugs:

If Intersect(Target, Range("A1:C10")) Is Nothing And _
Intersect(Target, Range("A20:C30")) Is Nothing Then Exit Sub

No explanation is given and I'm purplexed.

Michael

I too am perplexed.

Works for me.

Try this version.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:C10, A20:C30"
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Application.EnableEvents = False
On Error Resume Next
Target.Value = Range("Z1").Value
End If
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

On Fri, 18 Sep 2009 06:05:03 -0700 (PDT), Michael Lanier
wrote:

Thanks Luke. Unfortunately the following debugs:

If Intersect(Target, Range("A1:C10")) Is Nothing And _
Intersect(Target, Range("A20:C30")) Is Nothing Then Exit Sub

No explanation is given and I'm purplexed.

Michael

I can find no logical reason why none of the above macros work, but
for some reason, everything I try debugs. I can only conclue that
some other macro is in conflict although there is nothing that really
gives any such indication.

Thank each of you for your efforts. They are much appreciated.

Michael

FANTASTIC!

Bernie, your changes to my macro work. Thanks so very much. I do
have one last (I hope) question. When the macro executes, it
overrides my conditional formatting for each applicable cell. The
formatting is different throughout the range. Is there a way to avoid
changing the conditional formatting? Thanks.

Michael

Michael,

Copy the code below, right-click the sheet tab, select "View Code" and paste the code into the
window that appears.

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Range("A1:C10,A20:C30")) Is Nothing Then Exit Sub
If Target.Value < "" Then
Application.EnableEvents = False
Range("Z1").Copy Target
Application.EnableEvents = True
End If
End Sub


"Michael Lanier" wrote in message
...
Is there a way to enter a letter in a letter in a cell and once the
Enter key is struck, the letter will change to become a symbol that is
located in another cell? More specifically:

1) I have multiple ranges where I usually enter an "x". The ranges
are A1:C10 and A20:C30.

2) There is a symbol in Z1.

When I enter the "x" in multiples places within my ranges, I want the
"x" (or any other letter that might be entered) to instantly change to
the symbol when I stike the Enter key. Is this possible? Thanks for
any help you may offer.

Michael

Bernie,

Thanks for the macro. However, the following debugs:

If Target.Cells.Count 1 Then

I'm uncertain as to how to proceed.

Michael

Michael,

It sounds like you put the code into the wrong place. Right-click the sheet tab, select "View Code",
and paste the code into the window that appears.

The code needs to go into the worksheet codemodule, not a standard codemodule. The left-hand
dropdown above the code window should say "Worksheet" If it says "General" you pasted the code
into a standard codemodule.

HTH,
Bernie
MS Excel MVP


"Michael Lanier" wrote in message
...
Bernie,

Thanks for the macro. However, the following debugs:

If Target.Cells.Count 1 Then

I'm uncertain as to how to proceed.

Michael

Thanks for your help Bernie.

You were right. I misplaced my entry. (My status as a novice is
showing.) I tried to reconstruct a bit and enter the macro in
ThisWorkbook but again with no success. Perhaps you can quickly see
the problem. The range is a little different from my original sample
entry, as is the cell with the symbol. If you have a solution, it
would be much appreciated. Thanks again.

Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As
Range)
'MAKE 'X' CHANGE TO 'CHECK MARK' IN EXTBID & INTBID
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Worksheets("ExtBid").Range
("R2:T4,R7:T27,R42:T47")) Is Nothing Then Exit Sub
If Target.Value < "" Then
Application.EnableEvents = False
Worksheets("ExtBid").Range("AA4").Copy Target
Application.EnableEvents = True
End If
End Sub

Michael,

For the Thisworkbook code, you need to check the Sheet name:

Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
'MAKE 'X' CHANGE TO 'CHECK MARK' IN EXTBID & INTBID
If Target.Cells.Count 1 Then Exit Sub
If sh.Name = "ExtBid" Or sh.Name = "IntBid" Then
If Intersect(Target, sh.Range("R2:T4,R7:T27,R42:T47")) Is Nothing Then Exit Sub
If Target.Value < "" Then
Application.EnableEvents = False
Worksheets("ExtBid").Range("AA4").Copy Target
Application.EnableEvents = True
End If
End If
End Sub

If IntBid has a different symbol, then use

sh.Range("AA4").Copy Target

in place of


Worksheets("ExtBid").Range("AA4").Copy Target

--
HTH,
Bernie
MS Excel MVP


"Michael Lanier" wrote in message
...
Thanks for your help Bernie.

You were right. I misplaced my entry. (My status as a novice is
showing.) I tried to reconstruct a bit and enter the macro in
ThisWorkbook but again with no success. Perhaps you can quickly see
the problem. The range is a little different from my original sample
entry, as is the cell with the symbol. If you have a solution, it
would be much appreciated. Thanks again.

Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As
Range)
'MAKE 'X' CHANGE TO 'CHECK MARK' IN EXTBID & INTBID
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Worksheets("ExtBid").Range
("R2:T4,R7:T27,R42:T47")) Is Nothing Then Exit Sub
If Target.Value < "" Then
Application.EnableEvents = False
Worksheets("ExtBid").Range("AA4").Copy Target
Application.EnableEvents = True
End If
End Sub