defining a range as a string

[mshbhwn98]

I am working with a table someone else set up. I need to apply conditional formatting to a range. The range is defined by if a cell in column B is bold then the row of the table goes into the range. When I set the range rngCondFormatting I get an error which I assume is because the sting inside is too long. Could someone please confirm if this is correct and let me know what ways around this I could use?

Thanks

Private Sub Define_Range()
    
    Dim i As Integer, LastRow As Integer
    Dim strCondFormatting As String 'Conditional formatting range string
    Dim rngCondFormatting As Range  'Conditional formatting range
    'Find the last row
    LastRow = wks.Range("B1048576").End(xlUp).Row
    'Debug.Print LastRow
    
'Loop through the range
    For i = 17 To LastRow
        
        If wks.Range("B" & i).Font.Bold = True Then
            
            strCondFormatting = "B" & i & ":AD" & i & "," & strCondFormatting
        
        Else
        
        End If
    Next i
    
    'remove the comma at the end
    'strCondFormatting = """" & Left(strCondFormatting, Len(strCondFormatting) - 1) & """"
    strCondFormatting = Left(strCondFormatting, Len(strCondFormatting) - 1)
    Debug.Print Len(strCondFormatting)

Set rngCondFormatting = Range(strCondFormatting)
End Sub

[p45cal]

Do you need to define a string before defining a range?
Try:

Private Sub Define_Range2()
Dim LastRow As Range, rngCondFormatting As Range  'Conditional formatting range
'Set wks = ActiveSheet 'I didn't have a wks set.
With wks
  Set LastRow = .Cells(.Rows.Count, 2).End(xlUp)
  For Each cll In .Range(.Range("B17"), LastRow).Cells
    If cll.Font.Bold Then If rngCondFormatting Is Nothing Then Set rngCondFormatting = cll.Resize(, 29) Else Set rngCondFormatting = Union(rngCondFormatting, cll.Resize(, 29))
  Next cll
End With
'rngCondFormatting.Select
End Sub

[p45cal]

I've just had a thought; are you trying to set formatting for columns B:AD depending on whether the font in colmn B of a given row is bold?
If so you could set up conditional formatting for the whole range B17:ADnnn by first setting up a userdefined function to determine if a cell's font is bold:

Function IsBold(cel) As Boolean
IsBold = cel.Font.Bold
End Function

Then use it in conditional formatting:
Select the cells B17:ADnnn
bring up the conditional formatting dialogue then choose Use a Formula to determine…, and in the formula field have:
=isbold($B17)
then set your formatting etc.

On my machine, changing a cell in column B to bold text/not bold text resulted in the conditional formatting NOT updating straightaway, solved with the addtion of

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = True
End Sub

in the sheet's code module.

[mshbhwn98]

thanks p45cal. i'll take a look at both methods as im learning