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 FunctionThen 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 Subin the sheet's code module.

[mshbhwn98]

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