I have some programming experience, but do not know VB.

I have spreadsheets for a league schedule that end up with several
entries that are specifically "Bye".

I need a macro to search for each occurrence of "Bye" and then replace
it with "BYE" (upper case) but also with color red font.

The sheets vary (not all the same size since some leagues run longer).

Thanks for any help.

See one of actual league schedule spreadsheets at my league web site:
http://6amplayers.com/

click on matches on the left and then on "monday April 19, 2004" and
then on the "Excel-file" link...

Hi Jim,

Try this

For Each sh In ActiveWorkbook
Set oCell = sh.Cells.Find("bye",Lookat:=xlWhole,MatchCase:=Tru e)
If Not oCell Is Nothing Then
Do
oCell.Value = UCase(oCell.Value)
oCell.Font.ColorIndex = 3
Set oCell = .FindNext(c)
Loop While Not oCell Is Nothing
End If
Next sh

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Jim" wrote in message
om...
I have some programming experience, but do not know VB.

I have spreadsheets for a league schedule that end up with several
entries that are specifically "Bye".

I need a macro to search for each occurrence of "Bye" and then replace
it with "BYE" (upper case) but also with color red font.

The sheets vary (not all the same size since some leagues run longer).

Thanks for any help.

See one of actual league schedule spreadsheets at my league web site:
http://6amplayers.com/

click on matches on the left and then on "monday April 19, 2004" and
then on the "Excel-file" link...

Hi Jim

For changing the case you can use EditReplace in the Menu Bar

You can use the following example to change the color
http://www.rondebruin.nl/find.htm
Use the last macro on this page


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Jim" wrote in message om...
I have some programming experience, but do not know VB.

I have spreadsheets for a league schedule that end up with several
entries that are specifically "Bye".

I need a macro to search for each occurrence of "Bye" and then replace
it with "BYE" (upper case) but also with color red font.

The sheets vary (not all the same size since some leagues run longer).

Thanks for any help.

See one of actual league schedule spreadsheets at my league web site:
http://6amplayers.com/

click on matches on the left and then on "monday April 19, 2004" and
then on the "Excel-file" link...

Bob,

I get an error and debugger highlights .FindNext

Error is: Invalid or unqualified reference...

Thanks for a fix to this....

Jim




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

For Each sh In ActiveWorkbook
Set oCell = sh.Cells.Find("bye",Lookat:=xlWhole,MatchCase:=Tru e)
If Not oCell Is Nothing Then
Do
oCell.Value = UCase(oCell.Value)
oCell.Font.ColorIndex = 3
Set oCell = sh.Cells.FindNext(c)
Loop While Not oCell Is Nothing
End If
Next sh

--
Regards,
Tom Ogilvy

"Jim Mihalski" wrote in message
...

Bob,

I get an error and debugger highlights .FindNext

Error is: Invalid or unqualified reference...

Thanks for a fix to this....

Jim




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Bob,

I get an error on execution: invalid or unqualified reference.

this is highlighted by MVB: .FindNext

swap ptd & speedi and at and remove spaces for direct email

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

As previously stated:

For Each sh In ActiveWorkbook
Set oCell = sh.Cells.Find("bye",Lookat:=xlWhole,MatchCase:=Tru e)
If Not oCell Is Nothing Then
Do
oCell.Value = UCase(oCell.Value)
oCell.Font.ColorIndex = 3
Set oCell = sh.Cells.FindNext(c)
Loop While Not oCell Is Nothing
End If
Next sh

--
Regards,
Tom Ogilvy



"speedijim" <ptd at speedi.net wrote in message
...
Bob,

I get an error on execution: invalid or unqualified reference.

this is highlighted by MVB: .FindNext

swap ptd & speedi and at and remove spaces for direct email

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Use this

For Each sh In ActiveWorkbook.Sheets
Set oCell = sh.Cells.Find("bye", Lookat:=xlWhole, MatchCase:=True)
If Not oCell Is Nothing Then
Do
oCell.Value = UCase(oCell.Value)
oCell.Font.ColorIndex = 3
Set oCell = sh.Cells.FindNext(oCell)
Loop While Not oCell Is Nothing
End If
Next sh


--
Regards Ron de Bruin
http://www.rondebruin.nl


"speedijim" <ptd at speedi.net wrote in message ...
Bob,

I get an error on execution: invalid or unqualified reference.

this is highlighted by MVB: .FindNext

swap ptd & speedi and at and remove spaces for direct email

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Sorry Jim,

Didn't change one of the object references

For Each sh In ActiveWorkbook
Set oCell = sh.Cells.Find("bye",Lookat:=xlWhole,MatchCase:=Tru e)
If Not oCell Is Nothing Then
Do
oCell.Value = UCase(oCell.Value)
oCell.Font.ColorIndex = 3
Set oCell = .FindNext(oCell)
Loop While Not oCell Is Nothing
End If
Next sh

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Jim Mihalski" wrote in message
...

Bob,

I get an error and debugger highlights .FindNext

Error is: Invalid or unqualified reference...

Thanks for a fix to this....

Jim




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

now I get this error:
"object doesn't support this object or method"

I am doing a copy and paste of your code....


swap ptd & speedi and at and remove spaces

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Ron,

Thanks so much.... That was the one that worked...

I will take some time to try and determine why the otehrs didn't..

Thanks again!!!!

swap ptd & speedi and at and remove spaces

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

They didn't work because I didn't correct both errors (didn't expect two
errors in the same line).

Set oCell = sh.Cells.FindNext(c)

was the correction but it should have been

Set oCell = sh.Cells.FindNext(oCell)

Ron caught them both.

--
Regards,
Tom Ogilvy


"speedijim" <ptd at speedi.net wrote in message
...
Ron,

Thanks so much.... That was the one that worked...

I will take some time to try and determine why the otehrs didn't..

Thanks again!!!!

swap ptd & speedi and at and remove spaces

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Excel don't like this line also

For Each sh In ActiveWorkbook

I change it to

For Each sh In ActiveWorkbook.Sheets
or you can use
For Each sh In ActiveWorkbook.Worksheets



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Tom Ogilvy" wrote in message ...
They didn't work because I didn't correct both errors (didn't expect two
errors in the same line).

Set oCell = sh.Cells.FindNext(c)

was the correction but it should have been

Set oCell = sh.Cells.FindNext(oCell)

Ron caught them both.

--
Regards,
Tom Ogilvy


"speedijim" <ptd at speedi.net wrote in message
...
Ron,

Thanks so much.... That was the one that worked...

I will take some time to try and determine why the otehrs didn't..

Thanks again!!!!

swap ptd & speedi and at and remove spaces

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!