Repeat a macro

Hello. I want to repeat the below macro I created so that it searches for all instances of the text "Number" & shades 4 cells in the matching row. Can you tell me the easiest code to repeat the below macro, and if there is anything else i might have missed to perform this task? Thank you!!

1. turn on relative cell reference
2. search for the word "Number".
3. Press Home key to move cursor to first cell in the row (col A)
4. Select the next 4 consecutive cells in that row
5. apply a gray shading to the highlighted cells
6. return to the first cell (col A)
7. Move down to the next row.

Take a look at the attached file.
Below is the code. Change "color = 6" to adjust color.
modytrane

Please Login or Register  to view this content.

Taking a cue from the Guru, here's a more concise code:
It's installed in the attached file.

Please Login or Register  to view this content.

I copied & pasted the code from each suggestion above into VB. For the first item, I added the text: Sub test3() above the Private Sub Worksheet line. I get a "Compiled error, expected end sub" error. When I remove the "Sub test3()" text, I do not see a macro in the list. I tried the 2nd macro - removed all text in the vb module, copied & pasted in the code. Nothing happens - no search, no nothing.

Below is the macro I created just by using the record function. Is there code can i add to have the below repeat through the file to find all instances of the text "Number"? My apologies - I truly am a VB nubie....

Sub test()
'
' test Macro
' Macro recorded 10/26/2009 by kathy
'

'
Cells.Find(What:="Number", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
ActiveCell.Range("A1:E1").Select
With Selection.Interior
.ColorIndex = 40
.Pattern = xlSolid
End With
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

Here's a quick review of the two macros.
The first one I sent you goes in to Worksheet as an event macro.
It's written in to Worksheet_SelectionChange, so every time you select a different cell on that worksheet, the macro executes. It looks for any instance of "Number" in column A and colors next five cells on the same row.
You will not see this macro in the list under Tools>Macro.
This macro works automatically, so you don't have to trigger it.
Did you open the attachements I gave you? and did it work?

The next one that mrice sent you, can go in to a module.
You will see that in the macro list.
This macro requires a trigger. You can create a button and assign it to the macro. Or you can manually run it. This macro will color first four columns on the same row where "Number" is found.

See the attachement and see where these macros are installed. You can see how each one works by clicking on the appropriate button.

If you still have problems, attach your workbook here and someone will install the macro for you. [be sure to edit out any sensitive information].


hope this helps.
modytrane