Help with Macro to define a variable range in cell AA:AA

[gavhep]

Hi,

I have the following code which searches for keywords and highlights them. The problem I have is that the macro searches the whole spreadsheet, which when searching for lots of keywords, can take a long long time. I know that my keywords will only occur in column AA:AA and this range will be variable dependant on what the user pastes.

Is there a way to speed up my macro by asking it to search column AA:AA only, and (if it speeds up and more) only search the populated cells in AA:AA?

Code below. Thank you in advance.

Sub HighlightKeywords()
Dim X As Long, N As Long, L As Long, LKW As Long, Cell As Range
Dim Keywords As String, Txt As String
Dim KW() As String, Temp() As String
Keywords = "First word|second|third|fourth one"
KW = Split(Keywords, "|")
Application.ScreenUpdating = False
For Each Cell In ActiveSheet.UsedRange
Txt = Cell.Value
For X = 0 To UBound(KW)
LKW = Len(KW(X))
If InStr(1, Txt, KW(X), vbTextCompare) Then
Temp = Split(Txt, KW(X), , vbTextCompare)
L = 0
For N = 0 To UBound(Temp) - 1
L = L + Len(Temp(N))
Cell.Characters(L + 1, LKW).Font.Bold = True
Cell.Interior.Color = vbYellow
L = L + LKW
Next
End If
Next
Next
Application.ScreenUpdating = True
End Sub

 

[HaHoBe]

Hi gavhed,

if the values you search are strings/constants you may try and replace

For Each Cell In ActiveSheet.UsedRange

with

For Each Cell In ActiveSheet.Range("AA:AA").SpecialCells(xlCellTypeConstants, 23)

Code will error if there are no cells with constants found.

Ciao,
Holger