Another tough one: Find text, add text to cell with different color/font

[klarowe]

I want to try to create a macro that will search through column A on all sheets of my workbook for the text "Comments:". Then for each one that is found, add the text "Oxygen Cleaning Required" in bold, red font (colorindex = 3)... all within the same cell.

If that cannot be accomplished then I can unmerge the cells and put the new text (oxygen cleaning required) in column C of the same row as "comments:"

This will all be linked to either a button or upon workbook open, but if you can get me the bulk of the code, that will work.

Thanks yet again!!!

 

[MrKowz]

Try this Workbook_Open macro:

Public Sub Workbook_Open()
Dim ws      As Worksheet, _
    rng     As Range, _
    LR      As Long, _
    rng1    As String
    
For Each ws In ActiveWorkbook.Worksheets
    LR = ws.Range("A" & Rows.Count).End(xlUp).Row
    With ws.Range("A1:A" & LR)
        Set rng = .Find("Comments:", LookIn:=xlValues, lookat:=xlWhole)
        If Not rng Is Nothing Then
            rng1 = rng.Address
            Do
                With rng
                    .Value = rng.Value & " Oxygen Cleaning Required"
                    .Font.ColorIndex = 3
                End With
                Set rng = .FindNext(rng)
            Loop While Not rng Is Nothing And rng.Address <> rng1
        End If
    End With
Next ws
End Sub

 

[klarowe]

Didn't seem to work... but I just reviewed by project and I'm going to have to assign it to an array of worksheets (not all). Could you possible modify the code you posted to work with a checkbox(activex or form.. doesn't matter) and to add an array of say 5 sheets?
Thanks

 

[MrKowz]

Can you please provide more details about:

• The structure of your workbook
• What 5 sheets you want the code to run on
• What significance the checkboxes would hold?
• Are you looking for cells in column A that have only the string "Comments:" as its entire entry, or do you want also to have it add the phrase for any cell in column A that contains "Comments:". For example, in the following table, what cells do you want to add the phrase to?

Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD>Comments:</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD>Comments: Phooey!</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD>These are the Comments:</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD>Comments:</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD>The Comments: Where's the beef?</TD></TR></TBODY></TABLE><TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH>Sheet1</TH></TR></TD></THEAD><TBODY></TBODY></TABLE>

 

[klarowe]

Can you please provide more details about:

• The structure of your workbook
• What 5 sheets you want the code to run on
• What significance the checkboxes would hold?
• Are you looking for cells in column A that have only the string "Comments:" as its entire entry, or do you want also to have it add the phrase for any cell in column A that contains "Comments:". For example, in the following table, what cells do you want to add the phrase to?

The more I think about this, I don't think I'm going to be able to make it work because there are way too many variables. There are some parts that will contain "Comments:" in column A that I do not want to have the "Oxygen cleaning requried" linked to. There are also so many different formats of the sheets (and some workbooks have up to 40 sheets) that the code would take forever to run and just wouldn't be worth it.
Thank you very much for your help and sorry that I wasted your time.
Thanks

Kenny

 

[klarowe]

However, what I might be able to do is enter all of the cells into a named range and then simply do it that way (a little more work, but it will get everything I need). But is there a way to name a range with cells from different sheets?

 

[MrKowz]

However, what I might be able to do is enter all of the cells into a named range and then simply do it that way (a little more work, but it will get everything I need). But is there a way to name a range with cells from different sheets?

I don't believe there is. However, VBA can handle an array of cells to reference. For example:

Dim rng(1 To 5) As Range
rng(1) = Sheets("Sheet1").Range("A2:A90")
rng(2) = Sheets("Sheet1").Range("A100:A189")
rng(3) = Sheets("Sheet2").Range("A1:A1000")
rng(4) = Sheets("Sheet3").Range("A1:A10")
rng(5) = Sheets("Sheet4").Range("A1:A99")

 

[klarowe]

That will work. Then how do I enter that into the actual code I write up? I'm horrible when it comes to writing arrays...

 

[MrKowz]

Perhaps something like:

Public Sub FillComments()
Dim rng(1 To 5) As Range, _
    i           As Long, _
    rngFound    As Range, _
    rng1        As String
    
    
rng(1) = Sheets("Sheet1").Range("A2:A90")
rng(2) = Sheets("Sheet1").Range("A100:A189")
rng(3) = Sheets("Sheet2").Range("A1:A1000")
rng(4) = Sheets("Sheet3").Range("A1:A10")
rng(5) = Sheets("Sheet4").Range("A1:A99")
For i = 1 To UBound(rng)
    With rng(i)
        Set rngFound = .Find("Comments:", LookIn:=xlValues, lookat:=xlWhole)
        If Not rngFound Is Nothing Then
            rng1 = rngFound.Address
            Do
                With rngFound
                    .Value = rng.Value & " Oxygen Cleaning Required"
                    .Font.ColorIndex = 3
                End With
                Set rngFound = .FindNext(rngFound)
            Loop While Not rngFound Is Nothing And rngFound.Address <> rng1
        End If
    End With
Next i
End Sub

 

[klarowe]

I'll have to give that a try when I can. (just got pulled off this project and put on another.. lol).
Thanks again for your help!!!