Row Colors

B

Betty K

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
 
S

Simon Lloyd

Betty, i don't understand when you say "H", "A"....etc do you mean i
the letter is found in column M
In Excel 2007, need to differentiate rows by color

In column M
"H" = Color
"A" = Color
"P" = Color
"L" = Color

I will be printing data from the spreadsheet, so I'd like to us
lighte
shades of colors rather than the bright, primary colors that will sho
u
dark on printed pages

Thanking you in advance

Bett

--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com
 
B

Betty K

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 said:
Betty, i don't understand when you say "H", "A"....etc do you mean if
the letter is found in column M?
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
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=159853

Microsoft Office Help

.
 
S

Simon Lloyd

Well you cant use conditional formatting unless you are using xl2007 a
there are more than 3 conditions, so you need a VBA solution, add thi
to a standard module and run i
Code
-------------------
Sub row_colour(
Dim Rng As Range, MyCell As Rang
Application.ScreenUpdating = Fals
'change sheet to sui
Set Rng = Sheets("Sheet1").Range("M2:M" & Sheets("Sheet1").Range("M" & Rows.Count).End(xlUp).Row
For Each MyCell In Rn
Select Case MyCell.Valu
Case Is = "H
MyCell.EntireRow.Interior.ColorIndex =
Case Is = "A
MyCell.EntireRow.Interior.ColorIndex =
Case Is = "P
MyCell.EntireRow.Interior.ColorIndex =
Case Is = "L
MyCell.EntireRow.Interior.ColorIndex =
Case Els
MyCell.EntireRow.Interior.ColorIndex = xlNon
End Selec
Next MyCel
Application.ScreenUpdating = Tru
End Su
-------------------

*How to add and run a Macro*1. *Copy* the macro above placing th
cursor to the left of the code box hold the *CTRL & Left Click,* the
*Right Click* selected code and *Copy.*
2. Open your workbook
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* t
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 tim
you enter one of the desired letters or remove them the colour chang
will take place automaticall

Code
-------------------
Private Sub Worksheet_Change(ByVal Target As Range
If Target.Column <> 13 Then Exit Su
If Target.Cells.Count > 1 Then Exit Su
Select Case Target.Valu
Case Is = "H
Target.EntireRow.Interior.ColorIndex =
Case Is = "A
Target.EntireRow.Interior.ColorIndex =
Case Is = "P
Target.EntireRow.Interior.ColorIndex =
Case Is = "L
Target.EntireRow.Interior.ColorIndex =
Case Els
Target.EntireRow.Interior.ColorIndex = xlNon
End Selec
Application.ScreenUpdating = Tru
End Su
-------------------

*How to Save a Worksheet Event Macro*
1. *Copy* the macro above placing the cursor to the left of th
code box hold the *CTRL & Left Click,* then *Right Click* selected cod
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*

Yes, those are letters that are found in that column. The rows need t
b
colored based on the letters in column M





Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1


--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
 
B

Betty K

As indicated in my original question, I am using Excel 2007.

Simon Lloyd said:
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.*
2. Open your workbook
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*


Yes, those are letters that are found in that column. The rows need to
be
colored based on the letters in column M.


Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)


--
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
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=159853

Microsoft Office Help

.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top