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

0
Utf
4/20/2010 2:51:01 PM
excel.programming 6508 articles. 2 followers. Follow

6 Replies
709 Views

Similar Articles

[PageSpeed] 18

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
dguillett@gmail.com
"C" <C@discussions.microsoft.com> wrote in message 
news:1B8D6EA7-90D0-420D-AE52-7379167ACE4D@microsoft.com...
>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
> 

0
Don
4/20/2010 3:12:09 PM
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
> 
0
Utf
4/20/2010 3:14:10 PM
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
> > 
0
Utf
4/20/2010 3:46:01 PM
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
> 
0
Utf
4/20/2010 3:56:01 PM
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
dguillett@gmail.com
"Mike" <Mike@discussions.microsoft.com> wrote in message 
news:6A09545D-80C3-4943-BDE2-3B23F8B7B245@microsoft.com...
> 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
>> 

0
Don
4/20/2010 4:28:03 PM
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
> > 
0
Utf
4/20/2010 6:27:02 PM
Reply:

Similar Artilces:

double clicking and draging a column in a chart to chg data
in Excel 2003, double clicking on a column in a chart and then dragging the column up or down would change the data in a table upon which the chart depended. How does one do this in excel 2007? Tom Hi, That feature has been removed in 2007, there is no way to do it. If this helps, click the Yes button. -- Thanks, Shane Devenshire "Tom of inns" wrote: > in Excel 2003, double clicking on a column in a chart and then dragging the > column up or down would change the data in a table upon which the chart > depended. > > How does one do this in excel 2007? &g...

Problems migrating BCM data into CRM SB edition
Hi There I am having a problem migrating data from Business Contacts Manager (BCM) into CRM 3.0 Small Business edition. I have downloaded the BCM data migration pack and have followed the data migration documentation to the letter. I even cleaned up the BCM database prior to copying the files, checking them for errors using the Manage Database option in the Business Tools menu. It gets so far through the migration process and then bombs out. Here is the final few entries from the log file: 28/10/2006 12:18:53------>Transitioning to next screen. From: ConfigurationSummary screen. To: ...

transfer data from multiple columns to singlr column
I have data in form a d g b e h c f i (but larger scale) and I need it in a single column going a to z. Hi, highlight you data, copy, go to the column where you want to see the data, paste special, transpose "lc85" wrote: > I have data in form a d g > b e h > c f i (but larger scale) > and I need it in a single column going a to z. You up for using a macro? Sub ToOneColumn() 'dantuck Mar 7, 2007 &...

Summing Alternate Rows
We have spreadsheets with many rows and need to sum alternate rows. Is there a way to do this without entering a formula using the number of each row? -- KarenQ ------------------------------------------------------------------------ KarenQ's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=19608 View this thread: http://www.excelforum.com/showthread.php?threadid=395115 Try this: =SUMPRODUCT(--(MOD(ROW($A$1:$A$18),2)=1),$A$1:$A$18) That formula sums odd-row values in the range A1:A18. To sum even-row values the formula is: =SUMPRODUCT(--(MOD(ROW($A$1:$A$18...

Can't open 2005 data file after reinstalling Money 2005
I am experiencing a recurring problem. I have had to reinstall Windows XP and MS Money 2005. I am now unable to open my previously converted 2005 file or restore any backup version. I consistently get the following error message: "Money cannot locate filename or cannot open it, possibly because it is a read-only file, you do not have permission to change it, or your disk drive is write- protected. If you have chosen the correct file and it cannot be accessed, you will need to click OK and then Restore your most recent backup file." Any help or thoughts would be greatly ap...

code examples for BITS
Hi, I am looking for sample codes for programming background intelligent transfer service..I was able to see sample code in .NET, but I'm specifically looking for VC++ 6.0 (command-line application).Please help me.. Thanks, NKH ...

Access 2007 and Vista code problem
I am developing a database on a XP SP2 machine. On a form I have created some buttons with event procedures that run 2 queries and open a form. I sent the db to someone running Access 2007 on a Vista machine and nothing happens when she clicks the buttons and there are no messages. I know nothing about Vista so thanks in advance for any help. Tim Might be a reference problem, have them open the open a code window and click tools references. See what says missing and tell them what should be there, Add it and it should work. Duff "Tim Reid" <TimReid@discussions.micr...

Global Column Row Preview Font Size
I know I can change the column, row and preview font size for the current email folder's view, but how do I do it for all of the email folders? I have loads of email addresses each with lots of folders. I don't want to have to do each one at a time. Surely there must be a default font setting (even if it's only in the registry)? Thanks in advance, Tim. I too would love an answer to this. Ian "Timie Milie" <tim_milstead@yahoo.co.uk> wrote in message news:45782ee8$0$27107$db0fefd9@news.zen.co.uk... >I know I can change the column, row and preview font ...

Fractions Number format
Is it possible to do a custom number format for cells such that fractions are displayed. I'm guessing not because there'd be a limit on what fractions you could display, but I would probably only be interested in showing a half as the little 1/2 (alt,0189 I think) rather than .5 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.478 / Virus Database: 275 - Release Date: 06/05/03 Lee There are a limited number of fractions that can be shown under Format>Cells>Fractions. Gord Dibben Excel MVP - XL97 SR2 & ...

Copying Data in a cell in one sheet to a cell in another sheet
I've run into a problem trying to copy data from a cell in one sheet to another. I have a spreadsheet called "rating" which contains a number of formula that calculates a final number. I also have a spreadsheet called "Final" that copies over the information from "Rating". In "Final", I'm trying to copy a number from "Rating" into a particular cell. I put in =Rating! G89, but it won't work. When I press enter, a window pops up "Update value:Rating". I press enter again and in the cell where I want the number ...

Customer check data
When customers pay by check RMS asks for specific information such as drivers license number, routing number, account number, address and phone number. Does anyone have a report and or a way to extract this info from the database for cases when the check is returned for NSF? Please advise, Scott We can write you this report. Contact me for detail on price . Afshin Alikhani - [ afshin@retailrealm.co.uk ] CEO - Retail Realm = = = = = = = = = = "Scott Santorio" <scott@tt-newyork.com> wrote in message news:e8ZKkR6$HHA.3716@TK2MSFTNGP03.phx.gbl... > When customers pa...

Macro to seperate data
Hi I seem to be struggling to find a macro that will work in previous threads. In sheet 1 is a list of data in columns A:N and the number of rows will vary. It is a list of sales with each sale record ocuppying one row. The salesperson's name is in column C and each salesperson will have multiple entries. What I am trying to do is create a seperate summary sheet in the workbook for each salesperson. Therefore sheets 2 to 20 are templates that already exist with a different salesperson's name entered into cell C3 on each of them. I am trying to find a macro that ...

Need HELP! for Linking data
Could someone please direct me to where I can learn how to link date in a work book. i.e., I have individual pages for each subject but I need the data that is entered in these individual pages to transfer to the Master page without having to manually in put it.........TNX Bubey, There are not too many bits about linking worksheets or workbooks that I can find. But have a look at the links below, in case they give you the information you need. I think it is frustratingly one of those things which is very easy when you know how, or if you can get someone to actually show you, but if you hav...

Some Excel formatting functions taking a long time to work #2
Excel 2000 SP3 When I use some formatting functions for the first time in a session, such as bold, increasing font size etc, it takes up to 30 seconds to work. Meantime Excel is locked up until it completes that formatting call. I suspect faulty DLL? Has anyone experienced this? How to fix (other than a complete re- install) ? Any advice appreciated. Piri On Nov 5, 8:39=A0am, Piri <wiremu.pare...@hotmail.com> wrote: > Excel 2000 SP3 > When I use some formatting functions for the first time in a > session, such as bold, increasing font size etc, it takes =A0up to 30 > secon...

Merge code issues with Publisher 2003
It seems whenever the data source is altered, the merge code fields have to be reinserted in the Publisher document. Is this normal? The data source starts in Excel and is then saved as a .txt file to retain number formatting on final merge. ...

How to save Japanese characters in CSV format of Excel ?
Hi, I would like to know, how to save the Doubly-Byte characters.For example, Japanese in CSV of Excel.Here are the steps, I tried. 1. Open Excel 2. Copy pasted the Japanese charactesrs in a cell.It is displaying the characters correctly. 3. Save as CSV(Comma seperated file) It is saving as ??????. Thanks in advance for the info. ...

Can i use conditional formating on a cell when it contains a formula?
I am trying a "conditional formatting" on a cell that contains formula, but it didn't work. "If cell value is equal to 0 then font - white" This doesn't work, stays always. If i use this condition on a cell without formula it works just fine. Thank -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi are you sure your formula returns an exact zero?. Could you post the formul...

Macro
I need a macro that help me to transfer name and address information from an specific table in excel to a template in words on specific areas and then print the word document. The reason for this is that i need to create diferents letters to be sent to the customers from the excel table. Example of the table is: soc seg, customer name, child name, customer code, add 1 , add2, city, estate, zip code. all this information will be paste on word letter template on specific areas or fields. Any suggestion!!! -- nicoro Hi IMHO the best approach would be to set up a mail merge documen...

date modified
I have two sheets Data and Summary The "data" sheets macro extracts data from external file and paste into "Data" sheet Everytime the m acro is run to get latest data... The macro delete all contents of the "data" and then paste new data into the "data" sheet. Is there a way.. I can put a date on the "summary" sheet, when was the time the macro was run ( or in other words.. the data updated) This little macro records the date in the selected cell and formats it: Sub Macro2() Dim d As Date Dim s As String d = Now() s...

SUTA state message on Pay Code Integration
I'm having trouble importing pay codes that are based on another code, such as Sick and Vacation. These records get rejected with the error message "pay code 'V' requires a SUTA state". I have a SUTA state in the Emp Maint window and the HOURLY pay code already exists and contains a SUTA state. I tried mapping the SUTA State code in my file and even tried using a Constant value in Destination mapping but these codes will not import. I can go into the Pay Code Maintenance window and add these codes manually with no problem. What am I missing? ...

macros entering data
How do I create a macro that goes to one cell then waits until I enter new data, then goes to another cell and waits until I enter new data etc? thanks How about something like sub Enter_Data() dim NewValue NewValue = inputbox("Enter the value for cell A1: ") range("a1").value = NewValue NewValue = inputbox("Enter the value for cell G2: ") range("g2").value = NewValue NewValue = inputbox("Enter the value for cell I8: ") range("i8").value = NewValue end sub ...

MS Access 2007 code not running
I have created a brand new database in Access 2007 and linked some VB code to run on Form_Load. This code worked perfect with no problems the day I created it. Now today I opened the same form and it is not running the Form_Load code anymore (and yes it is still there, I did not delete it). I got frustrated so I copied and pasted the code to Form_Current and several On_Change events and set breakpoints at the beginning of all the events just to see if it was the Form_Load that was not triggering, but NO events are triggering my VB code. Any ideas? See this page: http://accessjunkie.com/fa...

Cell with large amount of data not showing all data
I'm running Excel 97. I have a cell with 358 words (1928 characters with spaces). Word wrap is on for the cell. Only part of the text is displayed even though the cell is big enough to show everything. If I make the cell wider (wider than a page) more of the text shows but not everything. I tried a new worksheet with the same text and had the same problem. Is this a known issue with excel? Is there a solution? Thanks, Brad Left to its own devices, excel will only show about 1000 characters in a cell. But you can add some alt-enters (to force a new line within the cell) and see more s...

Date format 04-11-06
Hi, Is there a possibility that the dates used in all the entities are not in the default format mm/dd/yyyy but in dd/mm/yyyy. I already adapted the Organisatonal settings, that only adapts the journal but nog the dates of an appointment. Does anyone have an idea? Thanks, ...

Adding blank "separator" Rows in a Pivot Table
Does anyone know how to insert blank rows into a pivot table (say, between groupings, etc.)? There has to be a way, since some of the various table formats that come with Excel include blank rows. Double-click the field button that you want to format Click Layout Add a checkmark to Insert blank line after each item Click OK, twice Bucko wrote: > Does anyone know how to insert blank rows into a pivot table (say, > between groupings, etc.)? There has to be a way, since some of the > various table formats that come with Excel include blank rows. -- Debra Dalgleish Contextures http...