Data-Entry shortcut

Howdy, folks:

I'm trying to make a macro that will help with data-entry. In two columns of each sheet (columns D and J), I'm entering school grade information ("K", "Pre-K", "1", etc, up to "12"). As I move off of each cell with data so-entered, I want to have the entry formated such that "1", becomes "1st", "12" becomes "12th", etc. I've put code into "Worksheet_SelectionChange" and the code works but...

1. The cell value is not changed upon leaving the cell, only after re-entering the cell. So, I type "1" in the cell, move on (the cell value remaining as "1"), then go back to that cell and the value becomes "1st" like I want. So each cell requires entry and then (for example), left arrow, then right arrow, then move on the next cell for data-entry. Since I'm only saving a few keystrokes for each cell, this approach is just about a wash. I need to have the data changed upon leaving the cell. Is there a way?

2. There are 50 or 60 sheets in the workBook and I have to copy that code onto each sheet. Is there a way to move it to the workBook level? Failing that, is there a way to ease the process of pasting that code to each of the sheets?

TIA, folks.

Bob

try

Private Sub Worksheet_Change(ByVal Target As Range)

instead

There's a similar event in the WorkBook events that will allow this to happen on each sheet

Option Explicit


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    With Target
        If .Count > 1 Then Exit Sub
        If .Column = 4 Or .Column = 10 Then
            Select Case .Value
                Case 1: .Value = .Value & "st"
                Case 2: .Value = .Value & "nd"
                Case 3: .Value = .Value & "rd"
                Case 4, 5, 6, 7, 8, 9, 10, 11, 12: .Value = .Value & "th"
                Case Else: Exit Sub
            End Select
        End If
    End With
End Sub

Copy the code
Select the workbook in which you want to store the code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
In the Project Explorer, find your workbook, and open the list of Microsoft Excel Objects
Right-click on the ThisWorkbook object, and choose View Code
Where the cursor is flashing, choose Edit | Paste

Your post does not comply with Rule 10 of our Forum RULES. Please acknowledge the response you receive, good or bad. If your problem is solved, please say so clearly.