rookie needs help with replace formatting and case change

  • Thread starter Thread starter Jim
  • Start date Start date
J

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...
 
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)
 
Bob,

I get an error and debugger highlights .FindNext

Error is: Invalid or unqualified reference...

Thanks for a fix to this....

Jim
 
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
 
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
 
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
 
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
 
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)
 
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
 
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
 
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.
 
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
 
Back
Top