I need help with a cell formula

[mrbrownsfan]

I want to make a certain cell, (H7) to convert the letters Y or N to display the word Yes or No. Is this possible? Is it better to create a macro to do this or to use a cell formula or VBA?

For example: To make filling out the spreadsheet quicker my boss just wants to type the letter "Y" in a cell and have it change to the word yes.

 

[alansidman]

Put this in the Worsksheet Change Event for the sheet you wish this to happen.

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
    If Target = Range("H7") Then
        If Target = "Y" Then Target = "Yes"
        If Target = "N" Then Target = "No"
    End If
End Sub

 

[Joe4]

One simple alternative may be to use Data Validation, choosing the List options, and setting "Yes;No" as your options.
Then they can just select the appropriate values from the drop-down.
And if they are all in a single-column, once you have select "Yes" or "No" once, you can just type in the first letter of each one, and it will autocomplete it.

Otherwise, you would probably want to use some automated VBA code that converts "Y to Yes" and "N to No" upon data entry.
If you want to go that route, then please let us know what range these entries will be in (I assume it is more than just H7).

 

[mrbrownsfan]

Thank you both so much for your help. Yes, It will be the range of H7 through H36. I will try both options to see which works best for him.

 

[Joe4]

Here is event procedure VBA code that will work on the entire range (H7:H36), and will handle it correctly regardless of whether they enter upper or lower case letters.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim isect As Range
    Dim cell As Range
    
    Set isect = Intersect(Target, Range("H7:H36"))
    
    If Not isect Is Nothing Then
        Application.EnableEvents = False
        For Each cell In isect
            Select Case UCase(cell)
                Case "Y"
                    cell = "Yes"
                Case "N"
                    cell = "No"
            End Select
        Next cell
        Application.EnableEvents = True
    End If
    
End Sub