mshbhwn98
09-17-2014, 09:21 AM
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
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