I am using Conditional Formatting to color a cell automatically.
Unfortunately using this method I am limited to 3 colours. Is there another
way to expand the color limit. I need to use 5 color options.

Numerical or text data?

If numerical JE McGimpsey has a method of formatting for up to 6 conditions.

http://www.mcgimpsey.com/excel/conditional6.html


Gord Dibben MS Excel MVP

On Wed, 15 Nov 2006 18:21:02 -0800, Dave Ah Ching <Dave Ah
wrote:

I am using Conditional Formatting to color a cell automatically.
Unfortunately using this method I am limited to 3 colours. Is there another
way to expand the color limit. I need to use 5 color options.

Thanks for your help Gord, I am actually wanting to automate the cell
background colour, not the font but this is good to know. Read the mcgimpsey
link which also mentioned Worksheet_Change() or Worksheet_Calculate() event
macros which is a bit beyond me. Is there any other easier way or Imight
just have to come up to speed on the macro side of things.

Thanks
Dave

"Gord Dibben" wrote:

Numerical or text data?

If numerical JE McGimpsey has a method of formatting for up to 6 conditions.

http://www.mcgimpsey.com/excel/conditional6.html


Gord Dibben MS Excel MVP

On Wed, 15 Nov 2006 18:21:02 -0800, Dave Ah Ching <Dave Ah
wrote:

I am using Conditional Formatting to color a cell automatically.
Unfortunately using this method I am limited to 3 colours. Is there another
way to expand the color limit. I need to use 5 color options.

You are stuck with VBA. Event code would do the trick.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim rng As Range
Dim vRngInput As Variant
Set vRngInput = Intersect(Target, Range("A:A"))
If vRngInput Is Nothing Then Exit Sub
For Each rng In vRngInput
'Determine the color
Select Case rng.Value
Case Is = 1: Num = 6 'yellow
Case Is = 2: Num = 10 'green
Case Is = 3: Num = 5 'blue
Case Is = 4: Num = 3 'red
Case Is = 5: Num = 46 'orange
End Select
'Apply the color
rng.Interior.ColorIndex = Num
Next rng
End Sub

Alternative solution........download Bob Phillips' CFPlus add-in. Allows up to
30 conditions.

http://www.xldynamic.com/source/xld.....Download.html

To implement the above event code right-click on the sheet tab and "View Code"

Copy/paste the code into that sheet module.

As you change the numbers in column A the background color will follow.

Adjust to suit.


Gord


On Wed, 15 Nov 2006 19:45:01 -0800, Dave Ah Ching
wrote:

Thanks for your help Gord, I am actually wanting to automate the cell
background colour, not the font but this is good to know. Read the mcgimpsey
link which also mentioned Worksheet_Change() or Worksheet_Calculate() event
macros which is a bit beyond me. Is there any other easier way or Imight
just have to come up to speed on the macro side of things.

Thanks
Dave

"Gord Dibben" wrote:

Numerical or text data?

If numerical JE McGimpsey has a method of formatting for up to 6 conditions.

http://www.mcgimpsey.com/excel/conditional6.html


Gord Dibben MS Excel MVP

On Wed, 15 Nov 2006 18:21:02 -0800, Dave Ah Ching <Dave Ah
wrote:

I am using Conditional Formatting to color a cell automatically.
Unfortunately using this method I am limited to 3 colours. Is there another
way to expand the color limit. I need to use 5 color options.

Thanks very muchagain Gord, I am using the CFplus download which has made
things a lot easier then trying to implement the VB script as this is a bit
beyond me unless I spend the time to play as I am not too sure how to apply
the script to my worksheet. But I'll probably look into the VB side of
things to extend my knowledge.

Cheers & thanks very much
Dave

"Gord Dibben" wrote:

You are stuck with VBA. Event code would do the trick.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim rng As Range
Dim vRngInput As Variant
Set vRngInput = Intersect(Target, Range("A:A"))
If vRngInput Is Nothing Then Exit Sub
For Each rng In vRngInput
'Determine the color
Select Case rng.Value
Case Is = 1: Num = 6 'yellow
Case Is = 2: Num = 10 'green
Case Is = 3: Num = 5 'blue
Case Is = 4: Num = 3 'red
Case Is = 5: Num = 46 'orange
End Select
'Apply the color
rng.Interior.ColorIndex = Num
Next rng
End Sub

Alternative solution........download Bob Phillips' CFPlus add-in. Allows up to
30 conditions.

http://www.xldynamic.com/source/xld.....Download.html

To implement the above event code right-click on the sheet tab and "View Code"

Copy/paste the code into that sheet module.

As you change the numbers in column A the background color will follow.

Adjust to suit.


Gord


On Wed, 15 Nov 2006 19:45:01 -0800, Dave Ah Ching
wrote:

Thanks for your help Gord, I am actually wanting to automate the cell
background colour, not the font but this is good to know. Read the mcgimpsey
link which also mentioned Worksheet_Change() or Worksheet_Calculate() event
macros which is a bit beyond me. Is there any other easier way or Imight
just have to come up to speed on the macro side of things.

Thanks
Dave

"Gord Dibben" wrote:

Numerical or text data?

If numerical JE McGimpsey has a method of formatting for up to 6 conditions.

http://www.mcgimpsey.com/excel/conditional6.html


Gord Dibben MS Excel MVP

On Wed, 15 Nov 2006 18:21:02 -0800, Dave Ah Ching <Dave Ah
wrote:

I am using Conditional Formatting to color a cell automatically.
Unfortunately using this method I am limited to 3 colours. Is there another
way to expand the color limit. I need to use 5 color options.