Existing code to bold specific text for a sheet...is it possible to tailor for a range instead a whole sheet?

[sadams1]

hello & thank you for your time checking this out! Many years ago this was cooked up by someone here & it has been extraordinarily useful. A suggested tweak was that this code be made to work only for a selected range of cells. I'm a hack & in no way have the experience or the ability to modify this change...would it even be possible?

Here's the code:



Public Sub wordBold()
Dim WkSh As Worksheet
Dim lRow As Long
Dim aText As Variant
Dim iIndex As Integer
Dim iPosition As Integer
Dim iLength As Integer
Set WkSh = Worksheets("data") 'your_sheetname
aText = Array("Green^") 'search_words Mon, Oct 29, 2012, update Sun, Jun 12, 16, Thu, Nov 11, 17
For lRow = 1 To WkSh.Cells(Rows.Count, 1).End(xlUp).Row
WkSh.Range("N" & lRow).Font.ColorIndex = xlAutomatic 'update column if needed!
For iIndex = 0 To UBound(aText)
iPosition = InStr(WkSh.Range("N" & lRow).Value, aText(iIndex)) 'update column if needed!
If iPosition > 0 Then
iLength = Len(aText(iIndex))
WkSh.Range("N" & lRow).Characters(Start:=iPosition, Length:=iLength).Font.Bold = True 'update column if needed!
WkSh.Range("N" & lRow).Characters(Start:=iPosition, Length:=iLength).Font.ColorIndex = 3 'red=3, black=1, blue 5
End If
Next iIndex
Next lRow
End Sub

 

[Kaper]

Hi,
At the moment the code does not work in a whole sheet, just in whole N column
If you want it to work for instance in range N100:N200 the change just one line:

For lRow = 1 To WkSh.Cells(Rows.Count, 1).End(xlUp).Row

to

For lRow = 100 To 200

If you want it to work literally in a selected range (so you select say C10:F20) and want code to work in each cell of the range try such version:

Public Sub wordBold2()
' Dim WkSh As Worksheet 'no longer needed as selection is in active Sheet :-)
Dim acell as  Range
Dim aText As Variant
Dim iIndex As Integer
Dim iPosition As Integer
Dim iLength As Integer

  aText = Array("Green^") 'search_words Mon, Oct 29, 2012, update Sun, Jun 12, 16, Thu, Nov 11, 17
  For each acell in selection 
     acell.Font.ColorIndex = xlAutomatic
     For iIndex = 0 To UBound(aText)
       iPosition = InStr(acell.Value, aText(iIndex)) 'update column if needed!
       If iPosition > 0 Then
          iLength = Len(aText(iIndex))
         acell.Characters(Start:=iPosition, Length:=iLength).Font.Bold = True
         acell.Characters(Start:=iPosition, Length:=iLength).Font.ColorIndex = 3 'red=3, black=1, blue 5
       End If
     Next iIndex
  Next acell
End Sub

 

[Logit]

Literally BOLD any selection :

Sub BoldSelectedRange()
    ' This macro will bold the font of the user's selected range
    Dim selectedRange As Range

    ' Set the selected range
    Set selectedRange = Selection

    ' Check if there is a selection
    If Not selectedRange Is Nothing Then
        ' Apply bold font to the selected range
        selectedRange.Font.Bold = True
    Else
        MsgBox "Please select a range first."
    End If
End Sub

And if I correctly understood your original request ... hope this works:

Public Sub wordBold()
    Dim WkSh As Worksheet
    Dim lRow As Long
    Dim aText As Variant
    Dim iIndex As Integer
    Dim iPosition As Integer
    Dim iLength As Integer

    Set WkSh = Worksheets("data") 'your_sheetname
    aText = Array("Green^") 'search_words

    For lRow = 1 To WkSh.Cells(Rows.Count, 1).End(xlUp).Row
        WkSh.Range("N" & lRow).Font.ColorIndex = xlAutomatic 'update column if needed!
        
        For iIndex = 0 To UBound(aText)
            iPosition = InStr(WkSh.Range("N" & lRow).Value, aText(iIndex)) 'update column if needed!
            
            If iPosition > 0 Then
                iLength = Len(aText(iIndex))
                WkSh.Range("N" & lRow).Characters(Start:=iPosition, Length:=iLength).Font.Bold = True 'update column if needed!
                WkSh.Range("N" & lRow).Characters(Start:=iPosition, Length:=iLength).Font.ColorIndex = 3 'red=3, black=1, blue 5
            End If
        Next iIndex
    Next lRow
End Sub

 

[sadams1]

hello guys! Greatly appreciate your time & am grateful for these options...they both are fantastic & can't thank you enough!

 

[Logit]

You are welcome.

 

[Kaper]

Glad we could help!