Conditional Format Font Color for more than 3 conditions

A B J K
7 Date Store # Date Store #
8
9 01/01/2009 1122
10 01/02/2009 3666
11 01/03/2009 1121
12 01/03/2009 2586
13 01/03/2009 4558
14 01/14/2009 6687
15 01/15/2009 N/A
16 01/15/2009 3652
17 01/16/2009 Sam

If any cell in column b =1122 then FONT COLOR is a color off the font chart
that I choose and every row in the worksheet with that store # will be that
color. I need this for ever item listed in column "B" above. Each Store #
will have a different color.

Furthermore, I need that same function sent to several other worksheet in
the workbook. I think that is =b,etc. Again, I need this for every store #
in column "B" listed above. Can I do this as a column or do I have to start
at B1 or can I choose column "B"? If I have to choose a cell pic one in
column "B" that does not have a heading?

I know that to put the same information in cells "J" & "K" of the same
worksheet then that cell then format painter, correct?

If you have answers please help. I already have almost 800 rows that need
to be formated in column "B" to include in at least 6 other worksheets in the
workbook.
--
abusymomforever

Excel 2003 and earlier - You can have formats for a maximum of three
different conditions (four if you count the default format)...[Excel 2007
does not have this limitation.]


You can conditionally format B1 and then Paint the whole Col or Select the
whole column and then enter the formula for FORMULA is
=A1=1122
and then paint the format over other columns...

You need to use a macro if you want to color more than three(plus one)
different ways...


"abusymomforever" wrote:

A B J K
7 Date Store # Date Store #
8
9 01/01/2009 1122
10 01/02/2009 3666
11 01/03/2009 1121
12 01/03/2009 2586
13 01/03/2009 4558
14 01/14/2009 6687
15 01/15/2009 N/A
16 01/15/2009 3652
17 01/16/2009 Sam

If any cell in column b =1122 then FONT COLOR is a color off the font chart
that I choose and every row in the worksheet with that store # will be that
color. I need this for ever item listed in column "B" above. Each Store #
will have a different color.

Furthermore, I need that same function sent to several other worksheet in
the workbook. I think that is =b,etc. Again, I need this for every store #
in column "B" listed above. Can I do this as a column or do I have to start
at B1 or can I choose column "B"? If I have to choose a cell pic one in
column "B" that does not have a heading?

I know that to put the same information in cells "J" & "K" of the same
worksheet then that cell then format painter, correct?

If you have answers please help. I already have almost 800 rows that need
to be formated in column "B" to include in at least 6 other worksheets in the
workbook.
--
abusymomforever

Hi,

Here is some sample code which you need to modify to meet your conditons:

Sub ColorCoding()
Dim cell As Range
For Each cell In Selection
With cell.EntireRow.Font
Select Case cell
Case 0 To 99
.ColorIndex = 38
Case 100 To 199
.ColorIndex = 44
Case 200 To 299
.ColorIndex = 36
Case 300 To 399
.ColorIndex = 35
Case 400 To 499
.ColorIndex = 34
Case 500 To 599
.ColorIndex = 37
Case 600 To 699
.ColorIndex = 39
Case 700 To 799
.ColorIndex = 7
Case 800 To 899
.ColorIndex = 4
Case Else
.ColorIndex = 47
End Select
End With
Next cell
End Sub

You will change my entries like 800 to 899 to read 3652 and so on.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"abusymomforever" wrote:

A B J K
7 Date Store # Date Store #
8
9 01/01/2009 1122
10 01/02/2009 3666
11 01/03/2009 1121
12 01/03/2009 2586
13 01/03/2009 4558
14 01/14/2009 6687
15 01/15/2009 N/A
16 01/15/2009 3652
17 01/16/2009 Sam

If any cell in column b =1122 then FONT COLOR is a color off the font chart
that I choose and every row in the worksheet with that store # will be that
color. I need this for ever item listed in column "B" above. Each Store #
will have a different color.

Furthermore, I need that same function sent to several other worksheet in
the workbook. I think that is =b,etc. Again, I need this for every store #
in column "B" listed above. Can I do this as a column or do I have to start
at B1 or can I choose column "B"? If I have to choose a cell pic one in
column "B" that does not have a heading?

I know that to put the same information in cells "J" & "K" of the same
worksheet then that cell then format painter, correct?

If you have answers please help. I already have almost 800 rows that need
to be formated in column "B" to include in at least 6 other worksheets in the
workbook.
--
abusymomforever

Unfortunately I am using Excel 2003. Thanks for the input I'll give it a try.
--
abusymomforever


"Sheeloo" wrote:

Excel 2003 and earlier - You can have formats for a maximum of three
different conditions (four if you count the default format)...[Excel 2007
does not have this limitation.]


You can conditionally format B1 and then Paint the whole Col or Select the
whole column and then enter the formula for FORMULA is
=A1=1122
and then paint the format over other columns...

You need to use a macro if you want to color more than three(plus one)
different ways...


"abusymomforever" wrote:

A B J K
7 Date Store # Date Store #
8
9 01/01/2009 1122
10 01/02/2009 3666
11 01/03/2009 1121
12 01/03/2009 2586
13 01/03/2009 4558
14 01/14/2009 6687
15 01/15/2009 N/A
16 01/15/2009 3652
17 01/16/2009 Sam

If any cell in column b =1122 then FONT COLOR is a color off the font chart
that I choose and every row in the worksheet with that store # will be that
color. I need this for ever item listed in column "B" above. Each Store #
will have a different color.

Furthermore, I need that same function sent to several other worksheet in
the workbook. I think that is =b,etc. Again, I need this for every store #
in column "B" listed above. Can I do this as a column or do I have to start
at B1 or can I choose column "B"? If I have to choose a cell pic one in
column "B" that does not have a heading?

I know that to put the same information in cells "J" & "K" of the same
worksheet then that cell then format painter, correct?

If you have answers please help. I already have almost 800 rows that need
to be formated in column "B" to include in at least 6 other worksheets in the
workbook.
--
abusymomforever