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
.
