rookie needs help with replace formatting and case change

[Jim]

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 Phillips]

Hi Jim,

Try this

For Each sh In ActiveWorkbook
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 = .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)

 

[Ron de Bruin]

Hi Jim

For changing the case you can use Edit>Replace 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

 

[Jim Mihalski]

Bob,

I get an error and debugger highlights .FindNext

Error is: Invalid or unqualified reference...

Thanks for a fix to this....

Jim

 

[Tom Ogilvy]

For Each sh In ActiveWorkbook
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(c)
Loop While Not oCell Is Nothing
End If
Next sh

 

[speedijim]

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

 

[Tom Ogilvy]

As previously stated:

For Each sh In ActiveWorkbook
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(c)
Loop While Not oCell Is Nothing
End If
Next sh

 

[Ron de Bruin]

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

 

[Bob Phillips]

Sorry Jim,

Didn't change one of the object references

For Each sh In ActiveWorkbook
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 = .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)

 

[speedijim]

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

 

[speedijim]

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

 

[Tom Ogilvy]

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.

 

[Ron de Bruin]

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