Change bold font using VBA

[Hudco]

I would appreciate it if someone out there can help me. I have the following VBA code:

  Range("B5:B74").Select
    Selection.ClearContents
    Range("B5").Select
    Dim bCell As Range
    Dim wt As Worksheet
    Set bCell = ThisWorkbook.Worksheets("Rec").Range("B5")
    For Each wt In ThisWorkbook.Worksheets
        If wt.Name > Worksheets("Rec").Range("S2") And wt.Name < Worksheets("Rec").Range("T2") Then
            wt.Range("F1").Copy
            ActiveSheet.Paste bCell
            Set bCell = bCell.Offset(1, 0)
        End If
    Next wt

And further on have the following:

With ActiveWorkbook.Worksheets("Rec").Sort
        .SetRange Range("A5:C" & LR)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

The area B5 to Last Row is copying in the bold font from the various sheets. How can I "unbold" the font?

Thank you in anticipation
Clyde

 

[Peter_SSs]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

There is generally no need to actually select cells/ranges in vba to work with them and selecting slows your code.

I have assumed that 'Rec' is the active sheet when the code get to what you have shown and that the values in S2 and T2 are text values. If that is not the case please five examples of what might be in those two cells.
Give this a try with a copy of your workbook.

Sub Test()
  Dim wt As Worksheet
  Dim RecLow As String, RecHigh As String
  Dim rw As Long
  
  RecLow = Range("S2").Value
  RecHigh = Range("T2").Value
  With Range("B5:B74")
    .ClearContents
    .Font.Bold = False
    rw = 1
    For Each wt In ThisWorkbook.Worksheets
      If wt.Name > RecLow And wt.Name < RecHigh Then
        .Cells(rw).Value = wt.Range("F1").Value
        rw = rw + 1
      End If
    Next wt
  End With
End Sub

 

[Hudco]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

There is generally no need to actually select cells/ranges in vba to work with them and selecting slows your code.

I have assumed that 'Rec' is the active sheet when the code get to what you have shown and that the values in S2 and T2 are text values. If that is not the case please five examples of what might be in those two cells.
Give this a try with a copy of your workbook.

Sub Test()
  Dim wt As Worksheet
  Dim RecLow As String, RecHigh As String
  Dim rw As Long
 
  RecLow = Range("S2").Value
  RecHigh = Range("T2").Value
  With Range("B5:B74")
    .ClearContents
    .Font.Bold = False
    rw = 1
    For Each wt In ThisWorkbook.Worksheets
      If wt.Name > RecLow And wt.Name < RecHigh Then
        .Cells(rw).Value = wt.Range("F1").Value
        rw = rw + 1
      End If
    Next wt
  End With
End Sub

Hi Peter_SSs,

That worked a treat.

Just got to find out if its the code that made it go slow or our computer/MS 365 setup. Have experienced some slowdowns particularly in Excel of late.

Many thanks for the solution. And I downloaded the file format for posting code in future.

Clyde

 

[Peter_SSs]

That worked a treat.

Good news. Thanks for letting us know.