asked on

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..........................

sdwalker

It would help a LOT if you attached the spreadsheet, if possible.

nutsch

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

BulldogMike

ASKER

attached is what I would like - I don't like the red cels

Test-Months.xls

BulldogMike

ASKER

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

ASKER CERTIFIED SOLUTION

nutsch

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BulldogMike

ASKER

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

BulldogMike

ASKER

Very knowledgeable and helpful

nutsch

Glad to help Mike. Thanks for the grade,

Thomas