Forum Discussion
Excel Autocorrect Formatting
This doesn't exist in Excel since, unlike Word, text processing is not its primary purpose.
You might use VBA code. In the attached workbook, Sheet1 will replace some abbreviations and italicize the replacement matching the entire cell. Sheet2 will do the same for part of the cell.
You'll have to allow macros.
The code is in the worksheet modules - right-click a sheet tab and select View Code from the context menu to see and edit it.
Apologies for being so code-illiterate, but is there also a way to have certain words be changed to not be italics? So basically run two VBA codes at the same time, one that changes it to italics, and one that changes it from italics to no italics?
The current code runs automatically when you enter something in a cell.
Removing Italics seems to be more suitable for a macro to be run separately. Or do you disagree?
- Totally agreed, I created a new line of code that removes italics, but I just need both of them to work together. Is there a way to do that?
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim arrFind
Dim arrReplace
Dim i As Long
If Target.CountLarge > 1 Then Exit Sub
' Modify and expand the arrays as needed
arrFind = Array("CAL", "ORE")
arrReplace = Array("CALIFORNIA DREAMING", "OREGON TRAIL")
Application.EnableEvents = False
On Error GoTo ExitHere
For i = 0 To UBound(arrFind)
If Target.Value = arrFind(i) Then
Target.Value = arrReplace(i)
Target.Font.Italic = True
Exit For
End If
Next i
ExitHere:
Application.EnableEvents = True
End Sub
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim arrFind
Dim arrReplace
Dim i As Long
If Target.CountLarge > 1 Then Exit Sub
' Modify and expand the arrays as needed
arrFind = Array("WIS", "FLO")
arrReplace = Array("WISCONSIN RAPIDS", "FLORIDA ANGELS")
Application.EnableEvents = False
On Error GoTo ExitHere
For i = 0 To UBound(arrFind)
If Target.Value = arrFind(i) Then
Target.Value = arrReplace(i)
Target.Font.Italic = False
Exit For
End If
Next i
ExitHere:
Application.EnableEvents = True
End SubIf you want to combine them:
Private Sub Worksheet_Change(ByVal Target As Range) Dim arrFind Dim arrReplace Dim i As Long If Target.CountLarge > 1 Then Exit Sub ' Modify and expand the arrays as needed arrFind = Array("CAL", "ORE") arrReplace = Array("CALIFORNIA DREAMING", "OREGON TRAIL") Application.EnableEvents = False On Error GoTo ExitHere For i = 0 To UBound(arrFind) If Target.Value = arrFind(i) Then Target.Value = arrReplace(i) Target.Font.Italic = True Exit For End If Next i ' Modify and expand the arrays as needed arrFind = Array("WIS", "FLO") arrReplace = Array("WISCONSIN RAPIDS", "FLORIDA ANGELS") Application.EnableEvents = False On Error GoTo ExitHere For i = 0 To UBound(arrFind) If Target.Value = arrFind(i) Then Target.Value = arrReplace(i) Target.Font.Italic = False Exit For End If Next i ExitHere: Application.EnableEvents = True End SubYour new code is also a Worksheet_Change event procedure. I thought that you agreed with me that it would be better to use an ordinary macro...