Can I enter a "Y" and return "Yes" in the same cell? PART 2

[rbbbbeee]

Last week I asked the question above and I got a couple great responces.
However, the programming response allowed me to only deal with one column and
I need to be able to enter a "Y" into any cell within 2 columns in the
worksheet and have it return in the same cell the word "YES". Can someone
please help me?

It was also suggested that I have the auto fill turned on and I would only
have to type the word "YES" once then "Y" and enter after that. This doesn't
seem to work in all areas of the worksheet. I end up having to type the
whole word in each section of the worksheet. Am I missing a setting or
something?

 

[Simon Lloyd]

Hi paste this in to the worksheet module, change the range to suit.

HTH

Regards,
Simon

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim mycell
Dim rng As Range
Set rng = Range("A1:C20")
For Each mycell In rng
If LCase(mycell.Text) = "y" Then
mycell.Value = "Yes"
ElseIf LCase(mycell.Text) <> "y" Then Exit Sub
End If
Next mycell
End Sub

 

[lexcel]

Right click on the tab of the sheet you wish to have this behaviour.
In the menu click "View Code"
In the VBA window that opens paste the following code (from '===== to
'=====)

' ==========================================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

Application.EnableEvents = False
For Each cell In Target
Select Case LCase(cell.Text)
Case "y", "yes"
cell = "Yes"
Case "n", "no"
cell = "No"
End Select
Next cell
Application.EnableEvents = True
End Sub
' ==========================================

I made it so that it will also take "no" for an answer. If you don't
want this, remove the lines
Case "n", "no"
cell = "No"

Greetz,

Lex