for each loop

[april]

i want to format each row in a spreadsheet that has the word "month" in bold.
i'm able to do it for 1 month but i want to loop through and do the other
lines with the word month
here is the macro as written--

Sub findMonth()


Dim FoundCell As Range
Dim FindWhat As String

FindWhat = "month"
With ActiveSheet.UsedRange
Set FoundCell = .Cells.Find(what:=FindWhat, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, lookat:=xlWhole, _
searchOrder:=xlByRows, _
searchDirection:=xlNext, _
MatchCase:=False)


FoundCell.Activate
Selection.EntireRow.Select
With Selection
.HorizontalAlignment = xlRight
.WrapText = True

End With
With Selection.Font
.Name = "Cambria"
.FontStyle = "Bold"
End With
End With
End Sub

thanks in advance for your help
aprilshowers

 

[Gary''s Student]

Sub BoldMonth()
Dim s As String
s = "month"
For Each r In ActiveSheet.UsedRange
If InStr(r.Value, s) > 0 Then
r.EntireRow.HorizontalAlignment = xlRight
r.EntireRow.WrapText = True
r.EntireRow.Font.Name = "Cambria"
r.EntireRow.Font.FontStyle = "Bold"
End If
Next
End Sub

 

[Paul C]

This should do the trick

Sub findMonth()


Dim FoundCell As Range
Dim FindWhat As String

FindWhat = "month"
For Each cell In ActiveSheet.UsedRange
If cell <> Empty And InStr(LCase(cell), FindWhat) > 0 Then
Set FoundCell = cell
FoundCell.Activate
Selection.EntireRow.Select
With Selection
.HorizontalAlignment = xlRight
.WrapText = True
End With
With Selection.Font
.Name = "Cambria"
.FontStyle = "Bold"
End With
End If
Next cell
End Sub

 

[Jim Thomlinson]

Here is your code expanded to do multiple finds. This code will be very
efficient if you have a large used range. The other posted solutions will
work just fine but if the used range is large they will take a while to
execute... I personally would always code it this way even if the used range
is currently small becuse what is now small may become large in the future...

Sub findMonth()

Dim rngToSearch As Range
Dim FoundCell As Range
Dim rngFoundAll As Range
Dim FindWhat As String
Dim strFirst As String

FindWhat = "month"

Set rngToSearch = ActiveSheet.Cells
Set FoundCell = rngToSearch.Find(what:=FindWhat, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
MatchCase:=False)

If Not FoundCell Is Nothing Then

Set rngFoundAll = FoundCell
strFirst = FoundCell.Address
Do
Set rngFoundAll = Union(FoundCell, rngFoundAll)
Set FoundCell = rngToSearch.FindNext(FoundCell)
Loop Until FoundCell.Address = strFirst
With rngFoundAll.EntireRow
.HorizontalAlignment = xlRight
.WrapText = True
.Font.Name = "Cambria"
.Font.FontStyle = "Bold"
End With
End If
End Sub

 

[Jim Thomlinson]

The ops code specifies xlWhole requiring a complete and exact match. Your
code looks for part matches...

 

[Jim Thomlinson]

The op specified xlWhole. Your code accepts partial matches.

 

[april]

Thank you for the answers. i am going to try your solutions. however, there
must be something wrong with my email since i wasn't notified that someone
had sent me a solution - i am almost positive that i checked the box "notify
me of replies." anyway i was looking for my post to say that i had found a
solution when i found all the replies.

the great thing about Excel is that there are multiple solutions to a
problem such as mine. i adapted a solution that i had saved some time ago
from your web site. it was HOW CAN I EXECUTE MY MACRO FOR EVERY ROW IN
WORKSHEET written 9/24/04.

now i'm going to try to understand your macros and learn something new.

thanks again--
aprilshowers