If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
for each loop
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 |
#2
|
|||
|
|||
for each loop
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 -- Gary''s Student - gsnu200908 "april" wrote: 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 |
#3
|
|||
|
|||
for each loop
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 -- If this helps, please remember to click yes. "april" wrote: 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 |
#4
|
|||
|
|||
for each loop
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 -- HTH... Jim Thomlinson "april" wrote: 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 |
#5
|
|||
|
|||
for each loop
The ops code specifies xlWhole requiring a complete and exact match. Your
code looks for part matches... -- HTH... Jim Thomlinson "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200908 "april" wrote: 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 |
#6
|
|||
|
|||
for each loop
The op specified xlWhole. Your code accepts partial matches.
-- HTH... Jim Thomlinson "Paul C" wrote: 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 -- If this helps, please remember to click yes. "april" wrote: 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 |
#7
|
|||
|
|||
for each loop
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 "Jim Thomlinson" wrote: 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 -- HTH... Jim Thomlinson "april" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|