Excel 2007 Modules/Macros/VB

[RedTen]

Howdy

I'd like to use VB to highlight specific text or format in a different colour within an entire sheet. I understand I probably need to use some VB to do this.

I've read about modules and found some code which should change the text. I don't understand how to link it altogether or make the code run.

Could someone point me in the right direction?

Public  Sub HighlightCodes()
  ' Select Cells to be highlighted and Run this Sub.
  Dim Codes(1 To 8)
  Dim Rng As Range
  Dim i As Long
  Dim StartPos As Long
  Codes(1) = "Specific text I need to highlight"
  Codes(2) = "Code 2"
  Codes(3) = "Code 3"
  Codes(4) = "Code 4"
  Codes(5) = "Code 5"
  Codes(6) = "Code 6"
  Codes(7) = "Code 7"
  Codes(8) = "Code 8"

  For Each Rng In Selection.Cells
    For i = 1 To 8
       StartPos = InStr(Rng.Value, Codes(i))
       If StartPos > 0 Then Rng.Characters(StartPos, Len(Codes(i))).Font.ColorIndex = 15
    Next i
  Next Rng
End Sub

I have that sat in a module so far..

 

[RedTen]

Why is it always when you ask the question you realise the answer.

I had the code in the wrong module, it was looking at a different spreadsheet.

Although, it is taking an age to run. Is it bad code?

 

[RedTen]

Ah bingo. I selected the whole sheet..

I did remove the other 7 entries though so not a total fail