#### Row Colors

```In Excel 2007, need to differentiate rows by color.

In column M,
"H" = Color 1
"A" = Color 2
"P" = Color 3
"L" = Color 4

I will be printing data from the spreadsheet, so I'd like to use lighter
shades of colors rather than the bright, primary colors that will show up
dark on printed pages.

Betty
```
 0
Utf
12/5/2009 12:11:01 PM
excel.worksheet.functions 4936 articles. 2 followers.

4 Replies
1031 Views

Similar Articles

[PageSpeed] 47

```Betty, i don't understand when you say "H", "A"....etc do you mean if
the letter is found in column M?

Betty K;578287 Wrote:
> In Excel 2007, need to differentiate rows by color.
>
> In column M,
> "H" = Color 1
> "A" = Color 2
> "P" = Color 3
> "L" = Color 4
>
> I will be printing data from the spreadsheet, so I'd like to use
> lighter
> shades of colors rather than the bright, primary colors that will show
> up
> dark on printed pages.
>
>
> Betty

--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1

[url=&quot;http://www.thecodecage.com&quot;]Microsoft Office Help[/url]

```
 0
Simon
12/5/2009 2:04:25 PM
```Yes, those are letters that are found in that column. The rows need to be
colored based on the letters in column M.

"Simon Lloyd" wrote:

>
> Betty, i don't understand when you say "H", "A"....etc do you mean if
> the letter is found in column M?
>
> Betty K;578287 Wrote:
> > In Excel 2007, need to differentiate rows by color.
> >
> > In column M,
> > "H" = Color 1
> > "A" = Color 2
> > "P" = Color 3
> > "L" = Color 4
> >
> > I will be printing data from the spreadsheet, so I'd like to use
> > lighter
> > shades of colors rather than the bright, primary colors that will show
> > up
> > dark on printed pages.
> >
> > Thanking you in advance.
> >
> > Betty
>
>
> --
> Simon Lloyd
>
> Regards,
> Simon Lloyd
> 'Microsoft Office Help' (http://www.thecodecage.com)
> ------------------------------------------------------------------------
> Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
>
> [url="http://www.thecodecage.com"]Microsoft Office Help[/url]
>
> .
>
```
 0
Utf
12/6/2009 4:09:01 AM
```Well you cant use conditional formatting unless you are using xl2007 as
there are more than 3 conditions, so you need a VBA solution, add this
to a standard module and run it
Code:
--------------------
Sub row_colour()
Dim Rng As Range, MyCell As Range
Application.ScreenUpdating = False
'change sheet to suit
Set Rng = Sheets("Sheet1").Range("M2:M" & Sheets("Sheet1").Range("M" & Rows.Count).End(xlUp).Row)
For Each MyCell In Rng
Select Case MyCell.Value
Case Is = "H"
MyCell.EntireRow.Interior.ColorIndex = 3
Case Is = "A"
MyCell.EntireRow.Interior.ColorIndex = 4
Case Is = "P"
MyCell.EntireRow.Interior.ColorIndex = 5
Case Is = "L"
MyCell.EntireRow.Interior.ColorIndex = 6
Case Else
MyCell.EntireRow.Interior.ColorIndex = xlNone
End Select
Next MyCell
Application.ScreenUpdating = True
End Sub
--------------------

*How to add and run a Macro*1. *Copy* the macro above placing the
cursor to the left of the code box hold the *CTRL & Left Click,* then
*Right Click* selected code and *Copy.*
3. Press the keys *ALT+F11* to open the Visual Basic Editor
4. Press the keys *ALT+I* to activate the *Insert menu*
5. *Press M* to insert a *Standard Module*
6. *Paste* the code by pressing the keys *CTRL+V*
7. Make any custom changes to the macro if needed at this time.
8. *Save the Macro* by pressing the keys *CTRL+S*
9. Press the keys *ALT+Q* to exit the Editor, and return to Excel.

*To Run the Macro...*
To run the macro from Excel, open the workbook, and press *ALT+F8* to
display the *Run Macro Dialog*. Double Click the macro's name to *Run*
it.

Then add this next macro to the sheet your working with and every time
you enter one of the desired letters or remove them the colour change
will take place automatically

Code:
--------------------
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 13 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
Select Case Target.Value
Case Is = "H"
Target.EntireRow.Interior.ColorIndex = 3
Case Is = "A"
Target.EntireRow.Interior.ColorIndex = 4
Case Is = "P"
Target.EntireRow.Interior.ColorIndex = 5
Case Is = "L"
Target.EntireRow.Interior.ColorIndex = 6
Case Else
Target.EntireRow.Interior.ColorIndex = xlNone
End Select
Application.ScreenUpdating = True
End Sub
--------------------

*How to Save a Worksheet Event Macro*
1. *Copy* the macro above placing the cursor to the left of the
code box hold the *CTRL & Left Click,* then *Right Click* selected code
and *Copy.*
2. Open your Workbook and *Right Click* on the *Worksheet's Name Tab*
for the Worksheet the macro will run on.
3. *Left Click* on *View Code* in the pop up menu.
4. *Paste* the macro code using *CTRL+V*
5. Make any custom changes to the macro if needed at this time.
6. *Save* the macro in your Workbook using *CTRL+S*

Betty K;578719 Wrote:
> Yes, those are letters that are found in that column. The rows need to
> be
> colored based on the letters in column M.
>
> "Simon Lloyd" wrote:
>
> >
> > Betty, i don't understand when you say "H", "A"....etc do you mean if
> > the letter is found in column M?
> >
> > Betty K;578287 Wrote:
> > > In Excel 2007, need to differentiate rows by color.
> > >
> > > In column M,
> > > "H" = Color 1
> > > "A" = Color 2
> > > "P" = Color 3
> > > "L" = Color 4
> > >
> > > I will be printing data from the spreadsheet, so I'd like to use
> > > lighter
> > > shades of colors rather than the bright, primary colors that will
> show
> > > up
> > > dark on printed pages.
> > >
> > > Thanking you in advance.
> > >
> > > Betty
> >
> >
> > --
> > Simon Lloyd
> >
> > Regards,
> > Simon Lloyd
> > 'Microsoft Office Help' ('http://www.thecodecage.com'
> (http://www.thecodecage.com/))
> >
> ------------------------------------------------------------------------
> > Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon
> Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)
> > View this thread: 'Row Colors - The Code Cage Forums'
> >
> > 'Microsoft Office Help' (http://www.thecodecage.com/)
> >
> > .
> >

--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1

[url=&quot;http://www.thecodecage.com&quot;]Microsoft Office Help[/url]

```
 0
Simon
12/6/2009 11:34:12 AM
```As indicated in my original question, I am using Excel 2007.

"Simon Lloyd" wrote:

>
> Well you cant use conditional formatting unless you are using xl2007 as
> there are more than 3 conditions, so you need a VBA solution, add this
> to a standard module and run it
> Code:
> --------------------
>     Sub row_colour()
>   Dim Rng As Range, MyCell As Range
>   Application.ScreenUpdating = False
>   'change sheet to suit
>   Set Rng = Sheets("Sheet1").Range("M2:M" & Sheets("Sheet1").Range("M" & Rows.Count).End(xlUp).Row)
>   For Each MyCell In Rng
>   Select Case MyCell.Value
>   Case Is = "H"
>   MyCell.EntireRow.Interior.ColorIndex = 3
>   Case Is = "A"
>   MyCell.EntireRow.Interior.ColorIndex = 4
>   Case Is = "P"
>   MyCell.EntireRow.Interior.ColorIndex = 5
>   Case Is = "L"
>   MyCell.EntireRow.Interior.ColorIndex = 6
>   Case Else
>   MyCell.EntireRow.Interior.ColorIndex = xlNone
>   End Select
>   Next MyCell
>   Application.ScreenUpdating = True
>   End Sub
> --------------------
>
>
> *How to add and run a Macro*1. *Copy* the macro above placing the
> cursor to the left of the code box hold the *CTRL & Left Click,* then
> *Right Click* selected code and *Copy.*
> 3. Press the keys *ALT+F11* to open the Visual Basic Editor
> 4. Press the keys *ALT+I* to activate the *Insert menu*
> 5. *Press M* to insert a *Standard Module*
> 6. *Paste* the code by pressing the keys *CTRL+V*
> 7. Make any custom changes to the macro if needed at this time.
> 8. *Save the Macro* by pressing the keys *CTRL+S*
> 9. Press the keys *ALT+Q* to exit the Editor, and return to Excel.
>
> *To Run the Macro...*
> To run the macro from Excel, open the workbook, and press *ALT+F8* to
> display the *Run Macro Dialog*. Double Click the macro's name to *Run*
> it.
>
>
> Then add this next macro to the sheet your working with and every time
> you enter one of the desired letters or remove them the colour change
> will take place automatically
>
>
> Code:
> --------------------
>     Private Sub Worksheet_Change(ByVal Target As Range)
>   If Target.Column <> 13 Then Exit Sub
>   If Target.Cells.Count > 1 Then Exit Sub
>   Select Case Target.Value
>   Case Is = "H"
>   Target.EntireRow.Interior.ColorIndex = 3
>   Case Is = "A"
>   Target.EntireRow.Interior.ColorIndex = 4
>   Case Is = "P"
>   Target.EntireRow.Interior.ColorIndex = 5
>   Case Is = "L"
>   Target.EntireRow.Interior.ColorIndex = 6
>   Case Else
>   Target.EntireRow.Interior.ColorIndex = xlNone
>   End Select
>   Application.ScreenUpdating = True
>   End Sub
> --------------------
>
>
> *How to Save a Worksheet Event Macro*
> 1. *Copy* the macro above placing the cursor to the left of the
> code box hold the *CTRL & Left Click,* then *Right Click* selected code
> and *Copy.*
> 2. Open your Workbook and *Right Click* on the *Worksheet's Name Tab*
> for the Worksheet the macro will run on.
> 3. *Left Click* on *View Code* in the pop up menu.
> 4. *Paste* the macro code using *CTRL+V*
> 5. Make any custom changes to the macro if needed at this time.
> 6. *Save* the macro in your Workbook using *CTRL+S*
>
>
>
> Betty K;578719 Wrote:
> > Yes, those are letters that are found in that column. The rows need to
> > be
> > colored based on the letters in column M.
> >
> > "Simon Lloyd" wrote:
> >
> > >
> > > Betty, i don't understand when you say "H", "A"....etc do you mean if
> > > the letter is found in column M?
> > >
> > > Betty K;578287 Wrote:
> > > > In Excel 2007, need to differentiate rows by color.
> > > >
> > > > In column M,
> > > > "H" = Color 1
> > > > "A" = Color 2
> > > > "P" = Color 3
> > > > "L" = Color 4
> > > >
> > > > I will be printing data from the spreadsheet, so I'd like to use
> > > > lighter
> > > > shades of colors rather than the bright, primary colors that will
> > show
> > > > up
> > > > dark on printed pages.
> > > >
> > > > Thanking you in advance.
> > > >
> > > > Betty
> > >
> > >
> > > --
> > > Simon Lloyd
> > >
> > > Regards,
> > > Simon Lloyd
> > > 'Microsoft Office Help' ('http://www.thecodecage.com'
> > (http://www.thecodecage.com/))
> > >
> > ------------------------------------------------------------------------
> > > Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon
> > Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)
> > > View this thread: 'Row Colors - The Code Cage Forums'
> > >
> > > 'Microsoft Office Help' (http://www.thecodecage.com/)
> > >
> > > .
> > >
>
>
> --
> Simon Lloyd
>
> Regards,
> Simon Lloyd
> 'Microsoft Office Help' (http://www.thecodecage.com)
> ------------------------------------------------------------------------
> Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
>
> [url="http://www.thecodecage.com"]Microsoft Office Help[/url]
>
> .
>
```
 0
Utf
12/6/2009 3:39:01 PM

Similar Artilces:

How can I combine data that is in 2 rows to 1 row
Hello I have a file that has only 1 data point (name of an institution) in row 1 column 1 and then multiple data points (city of the institution and other results) in row 2 column 1-7. I would like to combine the data in consecutive rows into 1 so I can work (sort results in different columns) with the data. As you can imagine the file has 2000 rows but the data addresses only 1000 institutions. I Would appreciate any help thanks albert explained : > Hello > > I have a file that has only 1 data point (name of an institution) in row 1 > column 1 and then multiple data points...

Lookup value off by one row
Hi, Any help you can provide on this would be appreciated. I have a worksheet that identifies how far a number is from target. I created the lookup below to coorespond to the productivity increase for next year. =LOOKUP(C4,LookUpValues!\$A\$1:\$A\$402,LookUpValues!\$B\$1:\$B\$402) C4 is -10.9, in the lookupValues tab -10.9 is in row 93 column A as is the desired result return of 5.6 in column b, however the lookup function is returning 5.7. The issue may be that the data and the lookupValues tab numbers are not exactly the same. -10.90001 and -10.9004 may display the same, but w...

Adding the # of rows, not the contents...
On my sub-totals row, most of the columns are just added together for a sum figure. However, can I have a particular column tally how many columns it is counting? Specifically, I have a column for customer's names. I just want the subtotal bar to display how many customers there are in the column above it. Thanks! Jacob, Try changing the 'sum' function in the subtotal bar to 'count'. Or add a formula, for example if the customers are listed in Column A, then in any empty cell enter the formula =count(A:A) Charlie O'Neill "jacob farino" <jfarino@mind...

Colored cells are showing white on screen, want to see color?
I have sheet cells that I know are colored, but they show white on my system, and print grayscale, while showing their true colors on other office systems.... Does this article help http://support.microsoft.com/d��efault.aspx?kbid=320531 Changes to fill color and fill pattern are not displayed in Office programs -- HTH Bob Phillips "dean" <dean@discussions.microsoft.com> wrote in message news:B1990525-1D7B-4E94-9653-80074F9AAEFD@microsoft.com... > I have sheet cells that I know are colored, but they show white on my system, > and print grayscale, while showing ...

How do I rearrange (switch) the information in my columns to rows
Copy the data out of Publisher and into Excel. You can swap it out there with the Paste Special (transpose) command. -- JoAnn Paules MVP Microsoft [Publisher] ~~~~~ How to ask a question http://support.microsoft.com/KB/555375 "larrey" <larrey@discussions.microsoft.com> wrote in message news:35EA8C76-ACA3-44A8-9876-78AE787885B6@microsoft.com... > The dirty way in Publisher, high-light the table, format menu, table, cell properties tab, check *rotate text within AutoShape by 90 degrees* Rotate the entire table 90 degrees. -- Mary Sauer MSFT MVP http://office.m...

Deselect one of many non-adjacent rows selected
If you are going through a worksheet and selecting several rows to, for example, delete them and you accidentally select a row you didn't mean to, is there a way to deselect just that one row? I noticed that CTRL clicking on the row again does not do the trick. Thanks!! Nicolle Very annoying feature. You can add a couple of Chip Pearson macros to your Personal.xls that will allow the unchecking of a mistake. Assign to a button or to the right-click menu. See this thread for the macros and instructions for adding an item to the right-click menu. http://snipurl.com/bxoj Gord D...

hiding rows #3
I want to find a quick way to hide various rows in a spreadsheet that contain zero values. i.e. below example i want to hide the whole row 'oranges' sales apples 100 pears 200 oranges 0 bananas 30 I have quite a lot of rows that may need hiding and any help would be appreciated, thanks. data>filter>autofilter -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "karl middleton" <karl middleton@discussions.microsoft.com> wrote in message news:64150DD9-6E0D-460D-8D75-21117ED6C23F@microsoft.com... >I w...

EXCEL:printing row headings at the top of every page.
I have looked at the EXCEL help and am not able to get row headings at the top of every page. I see the sheet tab and I see where I am "supposed" to enter the rows to be printed at the top of every page. HOWEVER these cells are "grayed out" i.e. inaccessible. Is there something else I need to do before I can access this function? It seems simple enough, but I am not able to enter rows to be printed. Any ideas? Are the sheets grouped? as this will cause that to happen, in which case you need to ungroup and set the print area. -- Regards Ken.............

Can you add color to a graphic?
Im use to InDesign mainly and it has a way to colorize a grayscale or bitmap file on the document without having to manually do it in photoshop or some other program. Does Publisher have a simillar feature? answering my own question...right click on image, FORMAT PICTURE/PICTURE/RECOLOR :-) "Greg" wrote: > Im use to InDesign mainly and it has a way to colorize a grayscale or bitmap > file on the document without having to manually do it in photoshop or some > other program. Does Publisher have a simillar feature? ...

Inserting alternate blank rows in Excel
I have several spreadsheets with between 2,000 and 3,000 rows of data. I'm using them to import data into some accounting software. The format required by the software is that each row of data is separated by a blank row. Short of individually inserting several thousand rows, does anyone know another way of changing the layout of the spreadsheet? Well, you could add a helper column and enter 1, 3, 5, and copy down to the bottom of what you've got. You could then put 2,4,6,8 ... in blank rows until you have the max # of what you need. Then sort by the helper column. Make su...

Spot Color separations not printing correctly
I am trying to print seperations for a two color project ( black + one pantone color). When I print on my InkJet and Laser printer the seperations are screwed up. When I took the file to the printer for preflight they were OK ?? Anybody know why ? "Screwed up" how? How was job preflighted? "Stuart F." <sfirstdc@hotmail.com> wrote in message news:004501c352d6\$8c5f7150\$a601280a@phx.gbl... > I am trying to print seperations for a two color project > ( black + one pantone color). When I print on my InkJet > and Laser printer the seperations are screwed up...

I have a drawing of a Dell rack with individual Dell server box pics in it. I figured out how to link one server's picture (using insert hyperlink) to open a spreadsheet containing server stats. What my boss wants is for each individual server's picture, when clicked, to open to its row of data in said excel file. Does anyone have a clue as to how this is done, either thru hyperlink, macro, vba, vbs, or sql script for each server? It would be helpful if the solution could find the server name in the spreadsheet then go right to that row. Thanks very much from a 1st time vis...

macro to delete multiple rows
I have recorded, then modified, the following macro, which unprotects the sheet, aks if you are sure you want to delete the row, deletes the current row, copies down a cell in column A that numbers the row, and protects the sheet. Works fine, but if the user wants to delete not only the current row, but multiple rows below it also, the macro must be repeated. How can I accomplish deleting multiple rows, as indicated by the user? For example, rows 18 thru 20, or 29 thru 33, etc, or of course, just the current row, with the message box prompting the user for the number of rows to delete. Thanks...

Can one adjust Format Row Autofit to ensure WYSIWYG when printing
Does anyone know if you can adjust Format Rows Autofit to ensure WYSIWYG when printing. I am continually frustrated that when I print after using the autofit function that I get text cut in half and underlines missing. Autofit does not seem to work properly. Can it be set to slightly overcompensate perhaps? Hi Gray, this problem only happens when you're working in a zoo different from 100%; so if you're working with 115% and autofit th rows they won't show up properly when you print (they will be cutted) Also there is a problem you have to consider is when you use Bold o...

filter on the rows
Is there any possibility to make a filter on the rows -- shlomo ----------------------------------------------------------------------- shlomop's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=973 View this thread: http://www.excelforum.com/showthread.php?threadid=46709 As far as I know, no. Filters can only be applied to columns. You ma need to transpose your data to apply a filter. I'm sure if someone knows of a way (probably via VBA) they will post solution. Good Luck Bruc -- swatsp0 --------------------------------------------------------------...

VB
Can anyone tell me why Cells.Interior.ColorIndex = Int(Rnd * 56) + 1 works ok using the forms toolbar with a button click but does not work if you use the control box toolbar. Maria, What does "not work" actually mean? What version of Excel are you using? Open the Properties window for the control button and change the "TakeFocusOnClick" property to False. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com chip@cpearson.com "maria" <maria.stothard@sylvania-lighting.com> wrote in message news:07e...

Changing text/color after a time period?
I hope some of the brilliant Excel people here can help me out. What I am looking to do, is enter a date in a certain cell (if it helps conceptualize, the date that I ship something to someone). Is it possible to have that cell with the "ship date" in it, either change colors, or change the font size, or something, after 30 days have gone by? My reasoning is that the product I ship out, has to be back to me in 30 days, or a penalty will incur to the other party. I just want these fields that have exceeded the 30 day mark, to stick out, so I can easily identify them. Thanks for any h...

Formula Bar font color
Hi All! I don't know how I did this, but the information in the formula bar is showing up in white font... Please help! ... I want to change it back to black... TIA! LavaDude Try this (Think it's a windows setting) Right-click on any empty space in the desktop > choose Properties Select Appearance tab > Advanced button (in Win XP) In the Advanced Appearance dialog: Click on "Item" dropdown menu > choose Message Box Under "Color" for Font (in the line *below* "Item") Choose "Black" color > OK > Apply (and don't do i...

hide a row if the values in the cell are zero
HI Can sombody help I need to hide a row if the values in the cell are zero Thanks I think the easiest way to do this is to select your range and apply Data|filter|Autofilter. Then you can use the dropdown and do: does not equal 0. Rosco wrote: > > HI > > Can sombody help > I need to hide a row if the values in the cell are zero > > Thanks -- Dave Peterson ec35720@msn.com ...

Is there a limit on selecting individual rows in excel?
I am using VBA logic to set a variable holding a series of rows to be deleted. When I use this on small worksheets no problem. But in larger worksheets when the logic goes to "rangevariable.select" I get an error. There are approx 168 rows selected in this occurance. Just a guess... You have to have the worksheet that contains that range selected before you can select that range. rangevariable.parent.parent.activate 'workbook rangevariable.parent.select 'worksheet rangevariable.select 'range or just application.goto rangvariable, scroll:=t...