Modify Code to format entire row of data

[C]

I had the following VBA that I use to format my reports:

Option Explicit

Sub boldFillRangeIfCBoldOrBNull()

Dim i As Integer

For i = 1 To 1000

If Cells(i, "c").Font.Bold = True Then
Range(Cells(i, "a"), Cells(i, "O")).Font.Bold = True
Range(Cells(i, "a"), Cells(i, "O")).Interior.ColorIndex = 15
End If

If Cells(i, "B") = "" Then
Range(Cells(i, "A"), Cells(i, "O")).Interior.ColorIndex = 19
End If

Next i

Reporting need have changed and the range of data varies by customer. My
spreadsheet could contain 5 rows or 5000 rows and the data may spread over
col c or it could extend to col JJ.

Can someone give me some idea as to how to get this to work on the existing
data?

Many Thanks in advance.

End Sub

 

[Don Guillett]

If you want uniformity just change your "0" to "JJ"
if you just want the column cells for that row
'=========
lc=cells(i,columns.count).end(xltoleft).column

If Cells(i, "c").Font.Bold = True Then
Range(Cells(i, "a"), Cells(i, lc)).Font.Bold = True
Range(Cells(i, "a"), Cells(i, lc)).Interior.ColorIndex = 15
End If

If Cells(i, "B") = "" Then
Range(Cells(i, "A"), Cells(i, lc)).Interior.ColorIndex = 19
End If
'======

 

[Mike]

Sub test()
Dim lastrow As Long
lastrow = Range("C" & Rows.Count).End(xlUp).Row
For i = 1 To lastrow
If Cells(i, "C").Font.Bold = True Then
With Range(Cells(i, "A"), Cells(i, "O"))
.Font.Bold = True
.Interior.ColorIndex = 15
End With
End If
If Cells(i, "B") = "" Then
With Range(Cells(i, "A"), Cells(i, "O"))
.Interior.ColorIndex = 19
End With
End If
Next i
End Sub

 

[C]

Mike,
This takes care of the "don't know how many rows of data" but it only goes
to col O. The data my extend to JJ, KL. Can this be accounted for as well?

Many Thanks.

 

[Mike]

Sub test()
Dim lastrow As Long
lastrow = Range("C" & Rows.Count).End(xlUp).Row
Dim lastrowInColumn As Long
For i = 1 To lastrow
lastrowInColumn = Range("C" & i).End(xlToRight).Column
If Cells(i, "C").Font.Bold = True Then
With Range(Cells(i, "A"), Cells(i, lastrowInColumn))
.Font.Bold = True
.Interior.ColorIndex = 15
End With
End If
If Cells(i, "B") = "" Then
With Range(Cells(i, "A"), Cells(i, lastrowInColumn))
.Interior.ColorIndex = 19
End With
End If
Next i
End Sub

 

[Don Guillett]

What about spaces between col A and col JJ.use
lastrowInColumn = cells(i,columns.count).End(xlTotoleft).Column

 

[C]

Mike,
I am sorry, I must not have been specific enough in my earlier posts. The
rows work fine. The column end data should key off row one. Therefore if
the last column in row one is D then the rows would be formatted from A to D,
likewise if the last col in row one containing data were JJ then the rows
below would be formatted accordingly.
Thanks,