Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Modify RDB's Copy filtered data code to loop through multiple shee | Excel Programming | |||
Copying Conditional Data Bar Format by Row to entire sheet | Excel Discussion (Misc queries) | |||
copying data from other worksheet including entire format | Excel Discussion (Misc queries) | |||
Modify to only work with nominated range instead of entire sheet | Excel Programming | |||
Modify a conditional format formula in code? | Excel Programming |