Macro or VBA to Bold first 3 characters of a cell in the entirecolumn

[imelda1ab]

My macro to bold the first three characters of a cell works great but
I am struggling with enhancing the macro to automatically repeat for
the entire column "C" instead of running it cell by cell. Ideally,
the macro would run on open, but I'll settle for running it on
command.

Any help is greatly appreciated!

Dim StartChar As Integer
Dim BoldLen As Integer
StartChar = 3
BoldLen = 5
ActiveCell.Characters(StartChar, BoldLen).Font.Bold = True

 

[Ken Hudson]

Hi,

Copy this code into ThisWorkbook, not a module:

Private Sub Workbook_Open()

Dim Iloop As Double
Dim RowCount As Double
Dim StartChar As Integer
Dim BoldLen As Integer

StartChar = 3
BoldLen = 5
RowCount = Cells(Rows.Count, "C").End(xlUp).Row
For Iloop = 1 To RowCount
Cells(Iloop, "C").Characters(StartChar, BoldLen).Font.Bold = True
Next Iloop

End Sub

This will bold characters 3 through 5, not the first 3.

 

[Peter T]

Another one

Sub test()
Dim StartChar As Long
Dim BoldLen As Long
Dim rng As Range, ra As Range, cell As Range

StartChar = 3
BoldLen = 5

On Error Resume Next
Set rng = Range("C:C").SpecialCells(xlCellTypeConstants, 23)
If rng Is Nothing Then Exit Sub

For Each ra In rng.Areas
For Each cell In ra
cell.Select
cell.Font.Bold = False
If Len(cell) >= StartChar Then
cell.Characters(StartChar, BoldLen).Font.Bold = True
End If
Next
Next

End Sub

Your description and code example contradict in terms of which characters to
make bold, I've gone with the latter.

Regards,
Peter T

 

[imelda1ab]

Another one

Sub test()
Dim StartChar As Long
Dim BoldLen As Long
Dim rng As Range, ra As Range, cell As Range

    StartChar = 3
    BoldLen = 5

    On Error Resume Next
    Set rng = Range("C:C").SpecialCells(xlCellTypeConstants, 23)
    If rng Is Nothing Then Exit Sub

    For Each ra In rng.Areas
        For Each cell In ra
            cell.Select
            cell.Font.Bold = False
            If Len(cell) >= StartChar Then
                cell.Characters(StartChar, BoldLen).Font.Bold = True
            End If
        Next
    Next

End Sub

Your description and code example contradict in terms of which characters to
make bold, I've gone with the latter.

Regards,
Peter T








- Show quoted text -

Thank you both SO, SO VERY much! It works perfectly! Yes, I
contradicted myself and pasted the wrong code. The 3,5 was contained
in the the code I found in my search through groups (love the groups)
and saved in my "Keep this so I don't have to look for it or remember
which spreadsheet I used it in again" formula document. Thanks again,
you guys rock.