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?

 

[wozencl]

The spreadsheet it checks is determined by the current selection, the same is true of the number of cells it's checking too.

If you select an entire worksheet you're going to be checking a huge number of cells and if you're using the same code posted above it's doing it 8 times.

 

[RedTen]

Ah bingo. I selected the whole sheet..

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