Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 164
  • Last Modified:

Excel loop(?) coding to find and update all entries

How do I approach the following problem.  I used the record macro function to create the following code to bold and color the Months in a spreadsheet.  How can I modify it to "loop"(?) through the spreadsheet to find and update all the entries?

Thanks

Cells.Find(What:="January", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Replace What:="January", Replacement:="January", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=True
    Cells.FindNext(After:=ActiveCell).Activate
   
    Cells.Find(What:="February", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Replace What:="February", Replacement:="February", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=True
    Cells.FindNext(After:=ActiveCell).Activate


etc..........................
0
BulldogMike
Asked:
BulldogMike
  • 4
  • 3
1 Solution
 
sdwalkerCommented:
It would help a LOT if you attached the spreadsheet, if possible.
0
 
nutschCommented:
That code should do it, assuming you like red.

THomas
Sub BoldMonths()
Dim i As Long, rg As Range, j As Long, strMonth As String, lgCount As Long
Application.ScreenUpdating = False

Set rg = [a1]

For i = 1 To 12

    strMonth = Format(DateValue(i & "/1/2010"), "mmmm")
    j = WorksheetFunction.CountIf(ActiveSheet.UsedRange, "*" & strMonth & "*")

    
    For lgCount = 1 To j
            
        Set rg = Cells.Find(What:=strMonth, After:=rg, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
                
        rg.Font.Bold = True
        rg.Interior.ColorIndex = 3
        
    Next lgCount
    
    Set rg = [a1]
    Err.Clear
Next i

Application.ScreenUpdating = True
   

End Sub

Open in new window

0
 
BulldogMikeAuthor Commented:
attached is what I would like -  I don't like the red cels

Test-Months.xls
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
BulldogMikeAuthor Commented:
Thomas

It would be helpful to me if you could comment your code so I can figure out what its doing.  I'm not a professionally trained programmer so sometimes need a little more help  to understand.  

Thank You

Mike  
0
 
nutschCommented:
Mike,

Here is an update with comments. And BTW, I'm a trained professional, but definitely not a professionally trained programmer.

Thomas

Sub BoldMonths()
Dim i As Long, rg As Range, j As Long, strMonth As String, lgCount As Long

'disable screenupdate to speed up macro and avoid screen flickering
Application.ScreenUpdating = False

Set rg = [a1]

For i = 1 To 12 'loop for 12 possible months

    'generate a string with the month name in mmmm format
    strMonth = Format(DateValue(i & "/1/2010"), "mmmm")
    
    'count the number of cells with that month in them
    j = WorksheetFunction.CountIf(ActiveSheet.UsedRange, "*" & strMonth & "*")

    'loop for all instances found
    For lgCount = 1 To j
            
        'find range matching strMonth
        Set rg = Cells.Find(What:=strMonth, After:=rg, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
        
        'bold and color font on that range
        rg.Font.Bold = True
        rg.Font.Color = 16711680
    
    'next instance
    Next lgCount
    
    'reset range to re-start find at first cell
    Set rg = [a1]
    Err.Clear
Next i

'reenable screenupdate
Application.ScreenUpdating = True
   

End Sub

Open in new window

0
 
BulldogMikeAuthor Commented:
Thomas

Thanks for you help and advice - I'll be watching you.  I also am a trained professional, a pharmacist.  Your knowledge level is impressive.

Thanks Again

Mike
0
 
BulldogMikeAuthor Commented:
Very knowledgeable and helpful
0
 
nutschCommented:
Glad to help Mike. Thanks for the grade,

Thomas
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now