Modify Code to format entire row of data
 

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

Modify Code to format entire row of data
 

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
'======

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"C" wrote in message
...
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

Modify Code to format entire row of data
 

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" wrote:

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

Modify Code to format entire row of data
 

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" wrote:

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" wrote:

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

Modify Code to format entire row of data
 

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

"C" wrote:

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

Modify Code to format entire row of data
 

What about spaces between col A and col JJ.
lastrowInColumn = Range("C" & i).End(xlToRight).Column
use
lastrowInColumn = cells(i,columns.count).End(xlTotoleft).Column
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Mike" wrote in message
...
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

"C" wrote:

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

Modify Code to format entire row of data
 

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,

"Mike" wrote:

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

"C" wrote:

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