More than 3 Cond. Formats??

[RC@CRA]

Good morning all. I am using Excel 2003 and I have a worksheet where I need
to apply more than just 3 conditional formats. I have a list of "never
ending" sales and in the far right column I have the salesman's name that
corresponds with the account. I want to be able to highlight the account in a
color that represents the salesman (i.e. Joe = blue, Bob = green, John =
yellow, Matt = pink etc.). The problem is that I have (at least) 7 different
salesman. Is there a way to work around the limit of 3 cond. formats? Can I
write a macro and if so HOW?? (never worked w/macros). Thanks in advance for
your help!

 

[Luke M]

You'll need a macro. Right click on sheet tab, view code. Paste the following
in:

'=======
Sub ColorCheck()
Dim i As Integer
'Creates sample of colors, with each row number
'corresponding to that colorindex number
For i = 1 To 56
Cells(i, 1).Interior.ColorIndex = i
Next
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As String
'
Define the range to look at
For Each cell In Range("B2:B20")

x = UCase(cell.Value)
Select Case x

'Follow pattern, inputting names in UPPER case
Case "BOB JONES"
cell.Interior.ColorIndex = 1
cell.Font.ColorIndex = 2

Case "TOM SMITH"
cell.Interior.ColorIndex = 4
cell.Font.ColorIndex = 2

End Select
Next
End Sub
'========

The first macro is simply a reference macro to help you set things up. It
colors the cells in the first column of your sheet, creating a reference
between index number and what the actual color is. The second macro is what
you actually need. The parts you'll need to modify to suit is at the
beginning where you define the range of cells to look at. the second part is
adding additional outcomes, aka "cases" that you want formatting for. I
provided two examples cases to help you get started.

Once your done writing the macro, close the Visual Basic Editor, and you
should be good to go.

 

[RC@CRA]

Hello - This is the code I put in. I am apparently doing something wrong b/c
it isn't wroking and when I added a cell it gave me an erro message and then
trew up the VB editor.
I have never written a macro before...



'=======
Sub ColorCheck()
Dim i As Integer
'Creates sample of colors, with each row number
'corresponding to that colorindex number
For i = 1 To 56
Cells(i, 1).Interior.ColorIndex = i
Next
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As String
'
Define the range to look at
For Each cell In Range("H2:H1000")

x = UCase(cell.Value)
Select Case x

'Follow pattern, inputting names in UPPER case
Case "BM"
cell.Interior.ColorIndex = orange
cell.Font.ColorIndex = 1

Case "BW"
cell.Interior.ColorIndex = bright green
cell.Font.ColorIndex = 1

Case "RT"
cell.Interior.ColorIndex = pink
cell.Font.ColorIndex = 1

Case "TA"
cell.Interior.ColorIndex = yellow
cell.Font.ColorIndex = 1

Case "GW"
cell.Interior.ColorIndex = violet
cell.Font.ColorIndex = 1

Case "PW"
cell.Interior.ColorIndex = light blue
cell.Font.ColorIndex = 1

Case "MK"
cell.Interior.ColorIndex = light turquoise
cell.Font.ColorIndex = 1

End Select
Next
End Sub
'========

 

[Luke M]

ColorIndex must be number, you can't simply say "yellow" or "bright green".

If you run the first macro, ColorCheck, it will create a key you can use.
Where each colored cell corresponds to a color index number equal to row
number. So, black = 1, white = 2, red = 3, etc. (note that this is using
default color settings). Use the key to figure out what number you want to
use.

 

[RC@CRA]

Hi Luke!
I appreciate your help but I am still lost...
After your first post I was trying to find the codes the the MS pallette of
color so that I could plug them into the macro (you wrote for me) for each
name. I found this document on www.mvps.org that said some of the descriptive
names could be used in coding so I treid that approach.
From what you are saying here this first macro is suppose to take the cells
I reference and use those colors to create a color code for me??
So in other words, if I put my colors I want in M2:M7 it should assign a
code for that color that I can use in the second macro?
When I click run macro and select the "color code" macro it keeps telling me
to define the range to look in (which I beleive is part of the 2nd macro) and
it keeps
highlightingin yellow: Private Sub Worksheet_Change(ByVal Target As Range)...

You can laugh - I have no idea what I am doing! = )
Please advise - Thanks!!!!!!!!!