Highlighting certain words within cells for editing.

[AlexanderHUN]

Hi guys,
I have a question regarding highlighting certain words (not the whole cell) in excel. The idea is that i want to highlight select words withing cells for editing, just to make it visually clear what needs to be changed on the excelsheet.I know a similar feature like this exists in Word, where you can basically lock most of the words and only allow people to enter text in the highlighted areas. A solution like this would be preferable, I hope it is possible in excel.
Thanks in advane

 

[nemmi69]

This gives an example of hot to highlight specific text.

Sub HighlightStrings()
Application.ScreenUpdating = False
Dim Rng As Range
Dim cFnd As String
Dim xTmp As String
Dim xLoop As Long
Dim cUBnd As Long
Dim WordLen As Long
cFnd = InputBox("Enter the text string to highlight")
WordLen = Len(cFnd)
For Each Rng In Selection
  With Rng
    cUBnd = UBound(Split(Rng.Value, cFnd))
    If cUBnd > 0 Then
      xTmp = ""
      For xLoop = 0 To cUBnd - 1
        xTmp = xTmp & Split(Rng.Value, cFnd)(xLoop)
        .Characters(Start:=Len(xTmp) + 1, Length:=WordLen).Font.ColorIndex = 3
        xTmp = xTmp & cFnd
      Next
    End If
  End With
Next Rng
Application.ScreenUpdating = True
End Sub

 

[AlexanderHUN]

Thank you very much, however i´ve never used VBA, how do I use this exactly?

 

[snjpverma]

Thank you very much, however i´ve never used VBA, how do I use this exactly?

To use VBA code in Excel, you need to open the Visual Basic Editor. You can do this by clicking on the “Developer” tab in the Excel ribbon. If you don’t see the “Developer” tab, you can enable it by going to “File” > “Options” > “Customize Ribbon” and checking the “Developer” box.

Once you have opened the Visual Basic Editor, you can insert the VBA code by creating a new module. To do this, right-click on the “Modules” folder in the “Project Explorer” pane and select “Insert” > “Module” from the context menu.

You can then copy and paste the VBA code into the new module. Once you have pasted the code, you can close the Visual Basic Editor and return to Excel. You can then run the VBA code by clicking on the “Macros” button in the “Developer” tab and selecting the macro you want to run.

 

[nemmi69]

Ah, well this might not help if you do not know VBa but others may like it. Created a function to call

Function HighlightText(ByVal RngHlt As Range, ByVal cFnd As String, ClrIndx As Integer) As Boolean
Application.ScreenUpdating = False
Dim Rng As Range
Dim xTmp As String
Dim xLoop As Long
Dim cUBnd As Long
Dim WordLen As Long

On Error GoTo HghltErr

WordLen = Len(cFnd)
If WordLen <= 0 Then GoTo HghltErr

For Each Rng In RngHlt
  With Rng
    cUBnd = UBound(Split(Rng.Value, cFnd))
    If cUBnd = -1 Then GoTo HghltErr

    If cUBnd > 0 Then
      xTmp = ""
      For xLoop = 0 To cUBnd - 1
        xTmp = xTmp & Split(Rng.Value, cFnd)(xLoop)
        .Characters(Start:=Len(xTmp) + 1, Length:=WordLen).Font.ColorIndex = ClrIndx
        xTmp = xTmp & cFnd
      Next
    End If
  End With
Next Rng
HighlightText = True
Application.ScreenUpdating = True
Exit Function
 
HghltErr:
HighlightText = False
Application.ScreenUpdating = True
End Function

Sub TryText()
Dim TxtRng As Range
Dim Txt2Hlt As String
Dim ClrHighL As Integer
Dim Tok As Boolean

Set TxtRng = ActiveSheet.Range("B1:B2")
ClrHighL = 3
Txt2Hlt = "Lamb"

Tok = HighlightText(TxtRng, Txt2Hlt, ClrHighL)
MsgBox "Highlight OK? " & Tok
End Sub

 

[AlexanderHUN]

I´ve tried to run the first macro based on snjpverma´s reply and nothing happens sadly, the window pops up where i need to type in the words to be highlighted but after clicking okay nothing gets highlighted

​

 

[nemmi69]

Have you a range of cells actually highlighted as it works only on the selected cells?

 

[AlexanderHUN]

Makes sense now it works thank you! One more thing, is there a way to print the excel sheet without highlighting?

 

[nemmi69]

Simplest is

Sub PrintSheet()
ActiveSheet.PrintOut
End Sub

 

[AlexanderHUN]

Thank you