convert Excel Cell Value Automatically

[nt_beans]

So I want a formula or macro which automatically changes cell value whenever certain value is entered:
Example
if I enter C in A1 then cell should Automatically change to C-
if I enter KW in A1 then cell should automatically change to KW

so heres the conversion rule:

C changes to C-
W changes to W-
S changes to S-
SM changes to 06
BO changes to 07
KW changes to KW ( No Change )

Thanks in advance

 

[Joe4]

What range do you want this to be effective on?
Just cell A1, or other cells as well?

 

[nt_beans]

it can be several hundred cells within Column A

 

[Joe4]

Ok, here is code that applies to all cells in column A.
This code must be placed in the correct place to run automatically. The easiest way to get there is to go to the sheet you wish to have this run on, right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste the code in the resulting VBA Editor window:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim srng As Range
    Dim rng As Range
    Dim cell As Range
    
'   Specify range you want to apply this on
    Set srng = Range("A:A")
    
    Set rng = Intersect(Target, srng)
    
    If Not rng Is Nothing Then
        Application.EnableEvents = False
        For Each cell In rng
            Select Case cell
                Case "C", "W", "S"
                    cell = cell & "-"
                Case "SM"
                    cell.NumberFormat = "@"
                    cell = "06"
                Case "BO"
                    cell.NumberFormat = "@"
                    cell = "07"
            End Select
        Next cell
        Application.EnableEvents = True
    End If
        
End Sub

To have it run against any other range, just change this line to the range you want to apply it to:

    Set srng = Range("A:A")

 

[nt_beans]

Ok, here is code that applies to all cells in column A.
This code must be placed in the correct place to run automatically. The easiest way to get there is to go to the sheet you wish to have this run on, right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste the code in the resulting VBA Editor window:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim srng As Range
    Dim rng As Range
    Dim cell As Range
    
'   Specify range you want to apply this on
    Set srng = Range("A:A")
    
    Set rng = Intersect(Target, srng)
    
    If Not rng Is Nothing Then
        Application.EnableEvents = False
        For Each cell In rng
            Select Case cell
                Case "C", "W", "S"
                    cell = cell & "-"
                Case "SM"
                    cell.NumberFormat = "@"
                    cell = "06"
                Case "BO"
                    cell.NumberFormat = "@"
                    cell = "07"
            End Select
        Next cell
        Application.EnableEvents = True
    End If
        
End Sub

To have it run against any other range, just change this line to the range you want to apply it to:

    Set srng = Range("A:A")

First of all I apologize as I had no clue it got posted twice. Not sure when i click submit, screen went to my user id and password so i was not sure original was already posted.

 

[Joe4]

i was not sure original was already posted

You can always check first. Just open another browser tab, and log in and click on "Subscribed Threads" to see if your question appears there.

If you are having problems that you are getting logged out, if you are not on a public computer, you can click the "Remember Me" checkbox when logging in.
Then it won't log you out automatically after a period of time.

 

[nt_beans]

While trying to run this code its opening a pop up window with blank macro name and run option is greyed out.....

 

[Joe4]

You don't have to do anything to run the code.
If you placed it in the proper sheet module, and copied it exactly "as-is", it will run automatically as people make entries into column A.

 

[nt_beans]

You don't have to do anything to run the code.
If you placed it in the proper sheet module, and copied it exactly "as-is", it will run automatically as people make entries into column A.

BRILLIANT - you rock

thanks very much

 

[Eric W]

Here's a non-macro option. Click File > Options > Proofing > AutoCorrect Options... , Then in the Replace: box put C, and in the With: box put C-. Repeat with the other values you want to change. For SM, put '06 in the With: box. The quote mark in front of the 06 prevents Excel from converting it to a number. Case matters, too.

The downside of this is that it applies to EVERYTHING, every column, sheet, workbook you have. So the macro might be better since it's more targeted.