rookie needs help with replace formatting and case change
 

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...

rookie needs help with replace formatting and case change
 

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...

rookie needs help with replace formatting and case change
 

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...

rookie needs help with replace formatting and case change
 

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!

rookie needs help with replace formatting and case change
 

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!

rookie needs help with replace formatting and case change
 

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!

rookie needs help with replace formatting and case change
 

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!

rookie needs help with replace formatting and case change
 

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!

rookie needs help with replace formatting and case change
 

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!

rookie needs help with replace formatting and case change
 

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!

rookie needs help with replace formatting and case change
 

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!

rookie needs help with replace formatting and case change
 

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!

rookie needs help with replace formatting and case change
 

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!