Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How to Loop some code
I have the following:
Option Explicit Sub Format_forecast() Dim FoundCell As Range Dim FindWhat As String FindWhat = "Font" ' This section looks for the Forecast months and then changes font colour to blue With ActiveSheet.UsedRange Set FoundCell = .Cells.Find(What:=FindWhat, _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If FoundCell Is Nothing Then MsgBox "Not Found" Else FoundCell.Offset(4, 0).Font.ColorIndex = 5 End If End With End Sub This works fine for me and finds the first instance of the word "Font" and changes the font colour. However I want this code to keep going and find all the instances of "Font" and change the font colour. Help! -- http://www.redbrick.dcu.ie/~pele |
#2
|
|||
|
|||
find will find only the first find.
for subsequent finds you have to use <findnext there is a good url given here which will help you. the sub give therein can be modified( Dick Kusleika http://www.dicks-blog.com/archives/2...e-find-method/ -- remove $$$ from email addresss to send email ==================== Phil Osman wrote in message ... I have the following: Option Explicit Sub Format_forecast() Dim FoundCell As Range Dim FindWhat As String FindWhat = "Font" ' This section looks for the Forecast months and then changes font colour to blue With ActiveSheet.UsedRange Set FoundCell = .Cells.Find(What:=FindWhat, _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If FoundCell Is Nothing Then MsgBox "Not Found" Else FoundCell.Offset(4, 0).Font.ColorIndex = 5 End If End With End Sub This works fine for me and finds the first instance of the word "Font" and changes the font colour. However I want this code to keep going and find all the instances of "Font" and change the font colour. Help! -- http://www.redbrick.dcu.ie/~pele |
#3
|
|||
|
|||
The example in VBA help shows you how to do just that.
-- HTH RP (remove nothere from the email address if mailing direct) "Phil Osman" wrote in message ... I have the following: Option Explicit Sub Format_forecast() Dim FoundCell As Range Dim FindWhat As String FindWhat = "Font" ' This section looks for the Forecast months and then changes font colour to blue With ActiveSheet.UsedRange Set FoundCell = .Cells.Find(What:=FindWhat, _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If FoundCell Is Nothing Then MsgBox "Not Found" Else FoundCell.Offset(4, 0).Font.ColorIndex = 5 End If End With End Sub This works fine for me and finds the first instance of the word "Font" and changes the font colour. However I want this code to keep going and find all the instances of "Font" and change the font colour. Help! -- http://www.redbrick.dcu.ie/~pele |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change case...help please | Excel Worksheet Functions | |||
Macro for changing text to Proper Case | Excel Worksheet Functions | |||
Using other workbooks.. | Excel Worksheet Functions | |||
VB for excel, how do I loop through code | Excel Discussion (Misc queries) | |||
Make Change Case in Excel a format rather than formula | Excel Worksheet Functions |