VBA Macro to find specific text within cell range and style it bold

1
votes

I am trying to develop a Macro to find specific text in all Worksheets within a Workbook and style the text bold.

Here is what i have currently which works fine:

Sub Style_Worksheets()

Dim ws As Worksheet

For Each ws In Sheets
    ws.Activate

Dim sCellVal As String

sCellVal = Range("A1").Value
sCellVal = Range("A5").Value
sCellVal = Range("A7").Value
sCellVal = Range("B7").Value

If sCellVal Like "*Workflow Name:*" Or _
sCellVal Like "Events*" Or _
sCellVal Like "Event Name*" Or _
sCellVal Like "Tag File*" Then

Range("A1").Font.Bold = True
Range("A5").Font.Bold = True
Range("A7").Font.Bold = True
Range("B7").Font.Bold = True

End If
Next ws
End Sub

Now the problem I am currently facing is that I have specific text that in one Worksheet is in cell A16, but in another Worksheet is in A10.

I have over 100 Worksheets that need styling, and the specific text is in different cells for each Worksheet.

I would like the Macro to find specific text between cells A10 and A16 and if it finds the text, I want it to style it bold.

I have tried adding the following into its relevant places:

sCellVal = Range("A10:A16").Value

and:

sCellVal Like "Workflow Level Mappings*" Or _

and:

Range("A10:A16").Font.Bold = True

...but no joy.

Can anyone help me out?

Thanks,

A

2
vbaexcel
I would suggest looking into the Find method which will look for your specified text - looks like you'll need to loop if you have several alternative bits of text to look for. – SJR

2 Answers

6
votes

Give this a shot. Fully tested.

Option Explicit

Sub Style_Worksheets()

    Dim TestPhrases() As String
    TestPhrases = Split("Workflow Name:,Events,Event Name,Tag File", ",")

    Dim ws As Worksheet

    For Each ws In Worksheets

        Dim CheckCell As Range
        For Each CheckCell In ws.Range("A10:A16")

            Dim Looper As Integer
            For Looper = LBound(TestPhrases) To UBound(TestPhrases)

                If InStr(CheckCell.Value, TestPhrases(Looper)) Then
                    CheckCell.Font.Bold = True
                    Exit For
                End If


            Next Looper

        Next CheckCell

    Next ws

End Sub
2
votes

Just loop over the cells in question:

Sub Style_Worksheets()

    Dim ws As Worksheet, sCellVal As String
    Dim R As Range

    For Each ws In Sheets
        ws.Activate
        For Each R In Range("A1:A16")

            sCellVal = R.Text

            If sCellVal Like "*Workflow Name:*" Or _
                sCellVal Like "Events*" Or _
                sCellVal Like "Event Name*" Or _
                sCellVal Like "Tag File*" Then
                    R.Font.Bold = True
            End If
        Next R
    Next ws
End Sub