Can it be done to change a cell color using an if statement or some other
type of formula and NOT use conditional formating.

I have 12 different possible values that could populate a cell and would
like to change the cell to one of 12 different colors already associated to
the value.

Thanks

VBA could do it. Im not sure that an if statement could do it but, I dont
think it can

"Shu of AZ" wrote:

Can it be done to change a cell color using an if statement or some other
type of formula and NOT use conditional formating.

I have 12 different possible values that could populate a cell and would
like to change the cell to one of 12 different colors already associated to
the value.

Thanks

No, a formula cannot affect the format of a cell, only its value. If
you need more than 3 conditional formats, why not download Bob
Phillips' CFPlus, which will give you up to 30 conditional formats?

Hope this helps.

Pete

On Jun 30, 11:50*pm, Shu of AZ
wrote:
Can it be done to change a cell color using an if statement or some other
type of formula and NOT use conditional formating.

I have 12 different possible values that could populate a cell and would
like to change the cell to one of 12 different colors already associated to
the value.

Thanks

Here is a VBA example


'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
'etc.
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
__________________________________
HTH

Bob

"Shu of AZ" wrote in message
...
Can it be done to change a cell color using an if statement or some other
type of formula and NOT use conditional formating.

I have 12 different possible values that could populate a cell and would
like to change the cell to one of 12 different colors already associated
to
the value.

Thanks

Sorry, I forgot to tell you where to get it from - he

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

Hope this helps.

Pete

On Jul 1, 12:27*am, Pete_UK wrote:
No, a formula cannot affect the format of a cell, only its value. If
you need more than 3 conditional formats, why not download Bob
Phillips' CFPlus, which will give you up to 30 conditional formats?

Hope this helps.

Pete

On Jun 30, 11:50*pm, Shu of AZ
wrote:



Can it be done to change a cell color using an if statement or some other
type of formula and NOT use conditional formating.

I have 12 different possible values that could populate a cell and would
like to change the cell to one of 12 different colors already associated to
the value.

Thanks- Hide quoted text -

- Show quoted text -

Thanks Bob, quick question. Where 'Select Case .value' is involved. If my
'case values' are numbers such as 1, 2, 3, up to 12, or blank. Would the
following code be;

Select Case .Value
1: .Interior.ColorIndex = 3
etc.

Also were you wrote Const WS_RANGE As String = "H1:H10"
In my sheet, there are 12 cells in one column that could all have the
possible value of anything between 1 and 12 or be blank. Does that matter?

Hence;
1
2
4
6
9
12
blank
blank
blank
blank
blank
blank





"Bob Phillips" wrote:

Here is a VBA example


'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
'etc.
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
__________________________________
HTH

Bob

"Shu of AZ" wrote in message
...
Can it be done to change a cell color using an if statement or some other
type of formula and NOT use conditional formating.

I have 12 different possible values that could populate a cell and would
like to change the cell to one of 12 different colors already associated
to
the value.

Thanks

Hi,

1. First the answer to your last question - no it doesn't matter.
2. FYI you can format cells using a Format, Cells command but you are
limited to changing the color of the text and you are limited to a max of 4
colors.
3. In 2007 this can be handled with Conditional Formatting because the 3
conditions restriction in 2003 and earlier has been removed and the number of
condtions is limited only by memory.
4. You can simplify the code Bob sent to read:

Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error Resume Next
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target.Interior
Select Case Target
Case 1: .ColorIndex = 3 'red
Case 2: .ColorIndex = 6 'yellow
Case 3: .ColorIndex = 5 'blue
Case 4: .ColorIndex = 10 'green
End Select
End With
End If

End Sub

This works because formatting a cell does not trigger the Change event so
there is not need for Application.EnableEvent. Your code is shorter if you
use the With statement Target.Interior because you are going to repeat the
Interior property 12 times in your code and the Value property once. Further
since Value is the default property of a cell (target) you can omit it.
Finally since you don't need the Application.EnableEvent statements you can
change the error handler to On Error Resume Next.

I believe that all of these changes will still allow the code to perform
correctly.
--
Cheers,
Shane Devenshire


"Shu of AZ" wrote:

Thanks Bob, quick question. Where 'Select Case .value' is involved. If my
'case values' are numbers such as 1, 2, 3, up to 12, or blank. Would the
following code be;

Select Case .Value
1: .Interior.ColorIndex = 3
etc.

Also were you wrote Const WS_RANGE As String = "H1:H10"
In my sheet, there are 12 cells in one column that could all have the
possible value of anything between 1 and 12 or be blank. Does that matter?

Hence;
1
2
4
6
9
12
blank
blank
blank
blank
blank
blank





"Bob Phillips" wrote:

Here is a VBA example


'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
'etc.
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
__________________________________
HTH

Bob

"Shu of AZ" wrote in message
...
Can it be done to change a cell color using an if statement or some other
type of formula and NOT use conditional formating.

I have 12 different possible values that could populate a cell and would
like to change the cell to one of 12 different colors already associated
to
the value.

Thanks

Thanks Shane but I did not see the answer to my first question. Define what
Case 1, Case 2, Case 3.

Does this refer to what would be in my case,,, the number 1, the number 2
and so?
on.

Referencing your #2. I'm not sure what that mention does for me as I wrote
I have 12 different values and need 12 different colors.

Referencing your #3. I still work with 2003.

The Range is H70:H82
The values possible are 1 thru 12
The colors are White font over Red, Black font over White, White font over
Blue, Black font over Yellow, Yellow font over Green, Yellow font over Black,
Black font over Orange, Black font over Pink, Black font over Aqua, White
font over Purple, Red font over Gray, and Black font over Lime in the same
order as the numbers.


"ShaneDevenshire" wrote:

Hi,

1. First the answer to your last question - no it doesn't matter.
2. FYI you can format cells using a Format, Cells command but you are
limited to changing the color of the text and you are limited to a max of 4
colors.
3. In 2007 this can be handled with Conditional Formatting because the 3
conditions restriction in 2003 and earlier has been removed and the number of
condtions is limited only by memory.
4. You can simplify the code Bob sent to read:

Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error Resume Next
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target.Interior
Select Case Target
Case 1: .ColorIndex = 3 'red
Case 2: .ColorIndex = 6 'yellow
Case 3: .ColorIndex = 5 'blue
Case 4: .ColorIndex = 10 'green
End Select
End With
End If

End Sub

This works because formatting a cell does not trigger the Change event so
there is not need for Application.EnableEvent. Your code is shorter if you
use the With statement Target.Interior because you are going to repeat the
Interior property 12 times in your code and the Value property once. Further
since Value is the default property of a cell (target) you can omit it.
Finally since you don't need the Application.EnableEvent statements you can
change the error handler to On Error Resume Next.

I believe that all of these changes will still allow the code to perform
correctly.
--
Cheers,
Shane Devenshire


"Shu of AZ" wrote:

Thanks Bob, quick question. Where 'Select Case .value' is involved. If my
'case values' are numbers such as 1, 2, 3, up to 12, or blank. Would the
following code be;

Select Case .Value
1: .Interior.ColorIndex = 3
etc.

Also were you wrote Const WS_RANGE As String = "H1:H10"
In my sheet, there are 12 cells in one column that could all have the
possible value of anything between 1 and 12 or be blank. Does that matter?

Hence;
1
2
4
6
9
12
blank
blank
blank
blank
blank
blank





"Bob Phillips" wrote:

Here is a VBA example


'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
'etc.
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
__________________________________
HTH

Bob

"Shu of AZ" wrote in message
...
Can it be done to change a cell color using an if statement or some other
type of formula and NOT use conditional formating.

I have 12 different possible values that could populate a cell and would
like to change the cell to one of 12 different colors already associated
to
the value.

Thanks

Hi,

First question - "Can it be done to change a cell color using an if
statement or some other
type of formula and NOT use conditional formating." Answer - Yes with VBA.
VBA samples provided by Bob and myself. Item 4.
What is Case 1 and so on? - you type the number which you want to format
with a particular color after the word Case. In these examples we are saying
if you enter the number 1 in a cell color it red, if the number is 2 then
color it yellow and so on. The best way is to copy the
Case 1: .ColorIndex = 3
line down as many times as you need and change the number after the word
Case to the numbers you will be formatting.
The best way to determine the values you need for the ColorIndex is to turn
on the recorder and change a cell's color 12 times. Then stop the recorder.
Look at the code Excel has created there will be 12 different numbers. You
can manually put those number in the macro. Numbers run from 1 to 56 I
believe and have no relationship to the color. The code Bob and I suggested
should be put in the Sheet object in the VBE. This means choose Tools,
Macro, Visual Basic Editor. On the upper left side of the screen you will
see a window called the Project - VBAProject. In that window each workbook
you have open will be displayed. Click the + sign beside the workbook you
are in to display the various objects, - they may already be displayed. You
will see a Sheet listed for each sheet in your workbook. Double-click the one
that you want the formatting in. In the window on the right paste a copy of
our code in and modify it as desired.

The 'red
'yellow
and so on are not necessary, Bob added those so you would know what color
would probably be displayed if you used his values. You can take them out.


--
Cheers,
Shane Devenshire


"Shu of AZ" wrote:

Thanks Shane but I did not see the answer to my first question. Define what
Case 1, Case 2, Case 3.

Does this refer to what would be in my case,,, the number 1, the number 2
and so?
on.

Referencing your #2. I'm not sure what that mention does for me as I wrote
I have 12 different values and need 12 different colors.

Referencing your #3. I still work with 2003.

The Range is H70:H82
The values possible are 1 thru 12
The colors are White font over Red, Black font over White, White font over
Blue, Black font over Yellow, Yellow font over Green, Yellow font over Black,
Black font over Orange, Black font over Pink, Black font over Aqua, White
font over Purple, Red font over Gray, and Black font over Lime in the same
order as the numbers.


"ShaneDevenshire" wrote:

Hi,

1. First the answer to your last question - no it doesn't matter.
2. FYI you can format cells using a Format, Cells command but you are
limited to changing the color of the text and you are limited to a max of 4
colors.
3. In 2007 this can be handled with Conditional Formatting because the 3
conditions restriction in 2003 and earlier has been removed and the number of
condtions is limited only by memory.
4. You can simplify the code Bob sent to read:

Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error Resume Next
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target.Interior
Select Case Target
Case 1: .ColorIndex = 3 'red
Case 2: .ColorIndex = 6 'yellow
Case 3: .ColorIndex = 5 'blue
Case 4: .ColorIndex = 10 'green
End Select
End With
End If

End Sub

This works because formatting a cell does not trigger the Change event so
there is not need for Application.EnableEvent. Your code is shorter if you
use the With statement Target.Interior because you are going to repeat the
Interior property 12 times in your code and the Value property once. Further
since Value is the default property of a cell (target) you can omit it.
Finally since you don't need the Application.EnableEvent statements you can
change the error handler to On Error Resume Next.

I believe that all of these changes will still allow the code to perform
correctly.
--
Cheers,
Shane Devenshire


"Shu of AZ" wrote:

Thanks Bob, quick question. Where 'Select Case .value' is involved. If my
'case values' are numbers such as 1, 2, 3, up to 12, or blank. Would the
following code be;

Select Case .Value
1: .Interior.ColorIndex = 3
etc.

Also were you wrote Const WS_RANGE As String = "H1:H10"
In my sheet, there are 12 cells in one column that could all have the
possible value of anything between 1 and 12 or be blank. Does that matter?

Hence;
1
2
4
6
9
12
blank
blank
blank
blank
blank
blank





"Bob Phillips" wrote:

Here is a VBA example


'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
'etc.
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
__________________________________
HTH

Bob

"Shu of AZ" wrote in message
...
Can it be done to change a cell color using an if statement or some other
type of formula and NOT use conditional formating.

I have 12 different possible values that could populate a cell and would
like to change the cell to one of 12 different colors already associated
to
the value.

Thanks

Shane,
Thanks for the clarification. I wondered if the word 'Case' was just used
as and example and would not be used in the actual code. Thanks again. . .
Shu

"ShaneDevenshire" wrote:

Hi,

First question - "Can it be done to change a cell color using an if
statement or some other
type of formula and NOT use conditional formating." Answer - Yes with VBA.
VBA samples provided by Bob and myself. Item 4.
What is Case 1 and so on? - you type the number which you want to format
with a particular color after the word Case. In these examples we are saying
if you enter the number 1 in a cell color it red, if the number is 2 then
color it yellow and so on. The best way is to copy the
Case 1: .ColorIndex = 3
line down as many times as you need and change the number after the word
Case to the numbers you will be formatting.
The best way to determine the values you need for the ColorIndex is to turn
on the recorder and change a cell's color 12 times. Then stop the recorder.
Look at the code Excel has created there will be 12 different numbers. You
can manually put those number in the macro. Numbers run from 1 to 56 I
believe and have no relationship to the color. The code Bob and I suggested
should be put in the Sheet object in the VBE. This means choose Tools,
Macro, Visual Basic Editor. On the upper left side of the screen you will
see a window called the Project - VBAProject. In that window each workbook
you have open will be displayed. Click the + sign beside the workbook you
are in to display the various objects, - they may already be displayed. You
will see a Sheet listed for each sheet in your workbook. Double-click the one
that you want the formatting in. In the window on the right paste a copy of
our code in and modify it as desired.

The 'red
'yellow
and so on are not necessary, Bob added those so you would know what color
would probably be displayed if you used his values. You can take them out.


--
Cheers,
Shane Devenshire


"Shu of AZ" wrote:

Thanks Shane but I did not see the answer to my first question. Define what
Case 1, Case 2, Case 3.

Does this refer to what would be in my case,,, the number 1, the number 2
and so?
on.

Referencing your #2. I'm not sure what that mention does for me as I wrote
I have 12 different values and need 12 different colors.

Referencing your #3. I still work with 2003.

The Range is H70:H82
The values possible are 1 thru 12
The colors are White font over Red, Black font over White, White font over
Blue, Black font over Yellow, Yellow font over Green, Yellow font over Black,
Black font over Orange, Black font over Pink, Black font over Aqua, White
font over Purple, Red font over Gray, and Black font over Lime in the same
order as the numbers.


"ShaneDevenshire" wrote:

Hi,

1. First the answer to your last question - no it doesn't matter.
2. FYI you can format cells using a Format, Cells command but you are
limited to changing the color of the text and you are limited to a max of 4
colors.
3. In 2007 this can be handled with Conditional Formatting because the 3
conditions restriction in 2003 and earlier has been removed and the number of
condtions is limited only by memory.
4. You can simplify the code Bob sent to read:

Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error Resume Next
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target.Interior
Select Case Target
Case 1: .ColorIndex = 3 'red
Case 2: .ColorIndex = 6 'yellow
Case 3: .ColorIndex = 5 'blue
Case 4: .ColorIndex = 10 'green
End Select
End With
End If

End Sub

This works because formatting a cell does not trigger the Change event so
there is not need for Application.EnableEvent. Your code is shorter if you
use the With statement Target.Interior because you are going to repeat the
Interior property 12 times in your code and the Value property once. Further
since Value is the default property of a cell (target) you can omit it.
Finally since you don't need the Application.EnableEvent statements you can
change the error handler to On Error Resume Next.

I believe that all of these changes will still allow the code to perform
correctly.
--
Cheers,
Shane Devenshire


"Shu of AZ" wrote:

Thanks Bob, quick question. Where 'Select Case .value' is involved. If my
'case values' are numbers such as 1, 2, 3, up to 12, or blank. Would the
following code be;

Select Case .Value
1: .Interior.ColorIndex = 3
etc.

Also were you wrote Const WS_RANGE As String = "H1:H10"
In my sheet, there are 12 cells in one column that could all have the
possible value of anything between 1 and 12 or be blank. Does that matter?

Hence;
1
2
4
6
9
12
blank
blank
blank
blank
blank
blank





"Bob Phillips" wrote:

Here is a VBA example


'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
'etc.
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
__________________________________
HTH

Bob

"Shu of AZ" wrote in message
...
Can it be done to change a cell color using an if statement or some other
type of formula and NOT use conditional formating.

I have 12 different possible values that could populate a cell and would
like to change the cell to one of 12 different colors already associated
to
the value.

Thanks

another question if you don't mind. I have 12 sheets, named R1, R2, R3 etc.
Each sheet is actually a duplicate of the others but named differently as you
can see in the R1, R2 and so on. The code, will that work for all sheets as
each sheet has a different set of data in that range and needs to indicate
the range value by a color.

"ShaneDevenshire" wrote:

Hi,

First question - "Can it be done to change a cell color using an if
statement or some other
type of formula and NOT use conditional formating." Answer - Yes with VBA.
VBA samples provided by Bob and myself. Item 4.
What is Case 1 and so on? - you type the number which you want to format
with a particular color after the word Case. In these examples we are saying
if you enter the number 1 in a cell color it red, if the number is 2 then
color it yellow and so on. The best way is to copy the
Case 1: .ColorIndex = 3
line down as many times as you need and change the number after the word
Case to the numbers you will be formatting.
The best way to determine the values you need for the ColorIndex is to turn
on the recorder and change a cell's color 12 times. Then stop the recorder.
Look at the code Excel has created there will be 12 different numbers. You
can manually put those number in the macro. Numbers run from 1 to 56 I
believe and have no relationship to the color. The code Bob and I suggested
should be put in the Sheet object in the VBE. This means choose Tools,
Macro, Visual Basic Editor. On the upper left side of the screen you will
see a window called the Project - VBAProject. In that window each workbook
you have open will be displayed. Click the + sign beside the workbook you
are in to display the various objects, - they may already be displayed. You
will see a Sheet listed for each sheet in your workbook. Double-click the one
that you want the formatting in. In the window on the right paste a copy of
our code in and modify it as desired.

The 'red
'yellow
and so on are not necessary, Bob added those so you would know what color
would probably be displayed if you used his values. You can take them out.


--
Cheers,
Shane Devenshire


"Shu of AZ" wrote:

Thanks Shane but I did not see the answer to my first question. Define what
Case 1, Case 2, Case 3.

Does this refer to what would be in my case,,, the number 1, the number 2
and so?
on.

Referencing your #2. I'm not sure what that mention does for me as I wrote
I have 12 different values and need 12 different colors.

Referencing your #3. I still work with 2003.

The Range is H70:H82
The values possible are 1 thru 12
The colors are White font over Red, Black font over White, White font over
Blue, Black font over Yellow, Yellow font over Green, Yellow font over Black,
Black font over Orange, Black font over Pink, Black font over Aqua, White
font over Purple, Red font over Gray, and Black font over Lime in the same
order as the numbers.


"ShaneDevenshire" wrote:

Hi,

1. First the answer to your last question - no it doesn't matter.
2. FYI you can format cells using a Format, Cells command but you are
limited to changing the color of the text and you are limited to a max of 4
colors.
3. In 2007 this can be handled with Conditional Formatting because the 3
conditions restriction in 2003 and earlier has been removed and the number of
condtions is limited only by memory.
4. You can simplify the code Bob sent to read:

Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error Resume Next
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target.Interior
Select Case Target
Case 1: .ColorIndex = 3 'red
Case 2: .ColorIndex = 6 'yellow
Case 3: .ColorIndex = 5 'blue
Case 4: .ColorIndex = 10 'green
End Select
End With
End If

End Sub

This works because formatting a cell does not trigger the Change event so
there is not need for Application.EnableEvent. Your code is shorter if you
use the With statement Target.Interior because you are going to repeat the
Interior property 12 times in your code and the Value property once. Further
since Value is the default property of a cell (target) you can omit it.
Finally since you don't need the Application.EnableEvent statements you can
change the error handler to On Error Resume Next.

I believe that all of these changes will still allow the code to perform
correctly.
--
Cheers,
Shane Devenshire


"Shu of AZ" wrote:

Thanks Bob, quick question. Where 'Select Case .value' is involved. If my
'case values' are numbers such as 1, 2, 3, up to 12, or blank. Would the
following code be;

Select Case .Value
1: .Interior.ColorIndex = 3
etc.

Also were you wrote Const WS_RANGE As String = "H1:H10"
In my sheet, there are 12 cells in one column that could all have the
possible value of anything between 1 and 12 or be blank. Does that matter?

Hence;
1
2
4
6
9
12
blank
blank
blank
blank
blank
blank





"Bob Phillips" wrote:

Here is a VBA example


'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
'etc.
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
__________________________________
HTH

Bob

"Shu of AZ" wrote in message
...
Can it be done to change a cell color using an if statement or some other
type of formula and NOT use conditional formating.

I have 12 different possible values that could populate a cell and would
like to change the cell to one of 12 different colors already associated
to
the value.

Thanks

Can you clarify exactly what this means ... The code, will that work for all
sheets as each sheet has a different set of data in that range and needs to
indicate the range value by a colour.


--
__________________________________
HTH

Bob

"Shu of AZ" wrote in message
...
another question if you don't mind. I have 12 sheets, named R1, R2, R3
etc.
Each sheet is actually a duplicate of the others but named differently as
you
can see in the R1, R2 and so on. The code, will that work for all sheets
as
each sheet has a different set of data in that range and needs to indicate
the range value by a color.

"ShaneDevenshire" wrote:

Hi,

First question - "Can it be done to change a cell color using an if
statement or some other
type of formula and NOT use conditional formating." Answer - Yes with
VBA.
VBA samples provided by Bob and myself. Item 4.
What is Case 1 and so on? - you type the number which you want to format
with a particular color after the word Case. In these examples we are
saying
if you enter the number 1 in a cell color it red, if the number is 2 then
color it yellow and so on. The best way is to copy the
Case 1: .ColorIndex = 3
line down as many times as you need and change the number after the word
Case to the numbers you will be formatting.
The best way to determine the values you need for the ColorIndex is to
turn
on the recorder and change a cell's color 12 times. Then stop the
recorder.
Look at the code Excel has created there will be 12 different numbers.
You
can manually put those number in the macro. Numbers run from 1 to 56 I
believe and have no relationship to the color. The code Bob and I
suggested
should be put in the Sheet object in the VBE. This means choose Tools,
Macro, Visual Basic Editor. On the upper left side of the screen you
will
see a window called the Project - VBAProject. In that window each
workbook
you have open will be displayed. Click the + sign beside the workbook
you
are in to display the various objects, - they may already be displayed.
You
will see a Sheet listed for each sheet in your workbook. Double-click the
one
that you want the formatting in. In the window on the right paste a copy
of
our code in and modify it as desired.

The 'red
'yellow
and so on are not necessary, Bob added those so you would know what color
would probably be displayed if you used his values. You can take them
out.


--
Cheers,
Shane Devenshire


"Shu of AZ" wrote:

Thanks Shane but I did not see the answer to my first question. Define
what
Case 1, Case 2, Case 3.

Does this refer to what would be in my case,,, the number 1, the number
2
and so?
on.

Referencing your #2. I'm not sure what that mention does for me as I
wrote
I have 12 different values and need 12 different colors.

Referencing your #3. I still work with 2003.

The Range is H70:H82
The values possible are 1 thru 12
The colors are White font over Red, Black font over White, White font
over
Blue, Black font over Yellow, Yellow font over Green, Yellow font over
Black,
Black font over Orange, Black font over Pink, Black font over Aqua,
White
font over Purple, Red font over Gray, and Black font over Lime in the
same
order as the numbers.


"ShaneDevenshire" wrote:

Hi,

1. First the answer to your last question - no it doesn't matter.
2. FYI you can format cells using a Format, Cells command but you are
limited to changing the color of the text and you are limited to a
max of 4
colors.
3. In 2007 this can be handled with Conditional Formatting because
the 3
conditions restriction in 2003 and earlier has been removed and the
number of
condtions is limited only by memory.
4. You can simplify the code Bob sent to read:

Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error Resume Next
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target.Interior
Select Case Target
Case 1: .ColorIndex = 3 'red
Case 2: .ColorIndex = 6 'yellow
Case 3: .ColorIndex = 5 'blue
Case 4: .ColorIndex = 10 'green
End Select
End With
End If

End Sub

This works because formatting a cell does not trigger the Change
event so
there is not need for Application.EnableEvent. Your code is shorter
if you
use the With statement Target.Interior because you are going to
repeat the
Interior property 12 times in your code and the Value property once.
Further
since Value is the default property of a cell (target) you can omit
it.
Finally since you don't need the Application.EnableEvent statements
you can
change the error handler to On Error Resume Next.

I believe that all of these changes will still allow the code to
perform
correctly.
--
Cheers,
Shane Devenshire


"Shu of AZ" wrote:

Thanks Bob, quick question. Where 'Select Case .value' is
involved. If my
'case values' are numbers such as 1, 2, 3, up to 12, or blank.
Would the
following code be;

Select Case .Value
1: .Interior.ColorIndex = 3
etc.

Also were you wrote Const WS_RANGE As String = "H1:H10"
In my sheet, there are 12 cells in one column that could all have
the
possible value of anything between 1 and 12 or be blank. Does that
matter?

Hence;
1
2
4
6
9
12
blank
blank
blank
blank
blank
blank





"Bob Phillips" wrote:

Here is a VBA example


'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
'etc.
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
__________________________________
HTH

Bob

"Shu of AZ" wrote in message
...
Can it be done to change a cell color using an if statement or
some other
type of formula and NOT use conditional formating.

I have 12 different possible values that could populate a cell
and would
like to change the cell to one of 12 different colors already
associated
to
the value.

Thanks

Will this code you have provided me, work for each of the twelve sheets or do
I have to activate each sheet, then have the block of code, then activate
another sheet then the block of code and so on.

In the workbook, there are 12 sheets and each sheet is identical except the
name of the sheet, ( R1, R2, up to R12 ) The data loads into each sheet and
onto the same range but is entirely different on each sheet. I need to have
the code recognize which sheet it is on so the colors change according to the
Case # on each sheet.

"Bob Phillips" wrote:

Can you clarify exactly what this means ... The code, will that work for all
sheets as each sheet has a different set of data in that range and needs to
indicate the range value by a colour.


--
__________________________________
HTH

Bob

"Shu of AZ" wrote in message
...
another question if you don't mind. I have 12 sheets, named R1, R2, R3
etc.
Each sheet is actually a duplicate of the others but named differently as
you
can see in the R1, R2 and so on. The code, will that work for all sheets
as
each sheet has a different set of data in that range and needs to indicate
the range value by a color.

"ShaneDevenshire" wrote:

Hi,

First question - "Can it be done to change a cell color using an if
statement or some other
type of formula and NOT use conditional formating." Answer - Yes with
VBA.
VBA samples provided by Bob and myself. Item 4.
What is Case 1 and so on? - you type the number which you want to format
with a particular color after the word Case. In these examples we are
saying
if you enter the number 1 in a cell color it red, if the number is 2 then
color it yellow and so on. The best way is to copy the
Case 1: .ColorIndex = 3
line down as many times as you need and change the number after the word
Case to the numbers you will be formatting.
The best way to determine the values you need for the ColorIndex is to
turn
on the recorder and change a cell's color 12 times. Then stop the
recorder.
Look at the code Excel has created there will be 12 different numbers.
You
can manually put those number in the macro. Numbers run from 1 to 56 I
believe and have no relationship to the color. The code Bob and I
suggested
should be put in the Sheet object in the VBE. This means choose Tools,
Macro, Visual Basic Editor. On the upper left side of the screen you
will
see a window called the Project - VBAProject. In that window each
workbook
you have open will be displayed. Click the + sign beside the workbook
you
are in to display the various objects, - they may already be displayed.
You
will see a Sheet listed for each sheet in your workbook. Double-click the
one
that you want the formatting in. In the window on the right paste a copy
of
our code in and modify it as desired.

The 'red
'yellow
and so on are not necessary, Bob added those so you would know what color
would probably be displayed if you used his values. You can take them
out.


--
Cheers,
Shane Devenshire


"Shu of AZ" wrote:

Thanks Shane but I did not see the answer to my first question. Define
what
Case 1, Case 2, Case 3.

Does this refer to what would be in my case,,, the number 1, the number
2
and so?
on.

Referencing your #2. I'm not sure what that mention does for me as I
wrote
I have 12 different values and need 12 different colors.

Referencing your #3. I still work with 2003.

The Range is H70:H82
The values possible are 1 thru 12
The colors are White font over Red, Black font over White, White font
over
Blue, Black font over Yellow, Yellow font over Green, Yellow font over
Black,
Black font over Orange, Black font over Pink, Black font over Aqua,
White
font over Purple, Red font over Gray, and Black font over Lime in the
same
order as the numbers.


"ShaneDevenshire" wrote:

Hi,

1. First the answer to your last question - no it doesn't matter.
2. FYI you can format cells using a Format, Cells command but you are
limited to changing the color of the text and you are limited to a
max of 4
colors.
3. In 2007 this can be handled with Conditional Formatting because
the 3
conditions restriction in 2003 and earlier has been removed and the
number of
condtions is limited only by memory.
4. You can simplify the code Bob sent to read:

Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error Resume Next
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target.Interior
Select Case Target
Case 1: .ColorIndex = 3 'red
Case 2: .ColorIndex = 6 'yellow
Case 3: .ColorIndex = 5 'blue
Case 4: .ColorIndex = 10 'green
End Select
End With
End If

End Sub

This works because formatting a cell does not trigger the Change
event so
there is not need for Application.EnableEvent. Your code is shorter
if you
use the With statement Target.Interior because you are going to
repeat the
Interior property 12 times in your code and the Value property once.
Further
since Value is the default property of a cell (target) you can omit
it.
Finally since you don't need the Application.EnableEvent statements
you can
change the error handler to On Error Resume Next.

I believe that all of these changes will still allow the code to
perform
correctly.
--
Cheers,
Shane Devenshire


"Shu of AZ" wrote:

Thanks Bob, quick question. Where 'Select Case .value' is
involved. If my
'case values' are numbers such as 1, 2, 3, up to 12, or blank.
Would the
following code be;

Select Case .Value
1: .Interior.ColorIndex = 3
etc.

Also were you wrote Const WS_RANGE As String = "H1:H10"
In my sheet, there are 12 cells in one column that could all have
the
possible value of anything between 1 and 12 or be blank. Does that
matter?

Hence;
1
2
4
6
9
12
blank
blank
blank
blank
blank
blank





"Bob Phillips" wrote:

Here is a VBA example


'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
'etc.
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
__________________________________
HTH

Bob

"Shu of AZ" wrote in message
...
Can it be done to change a cell color using an if statement or
some other
type of formula and NOT use conditional formating.

I have 12 different possible values that could populate a cell
and would
like to change the cell to one of 12 different colors already
associated
to
the value.

Thanks

Thanks Shane and Bob,

The code works fantastic.

Although my foresight was lacking. Because of the color changes occuring, I
also have to be able to change the color of the font. MY ATTEMPT failed as
seen below in Case 1.
Any assistance in correcting the code line for font color would be
appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "o5:o43"

On Error Resume Next
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target.Interior
Select Case Target
Case 1: .ColorIndex = 1
Case 1: .Font.ColorIndex = 3 <====== MY ATTEMPT
Case 2: .ColorIndex = 6 'yellow
Case 3: .ColorIndex = 5 'blue
Case 4: .ColorIndex = 10 'green
End Select
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

"ShaneDevenshire" wrote:

Hi,

First question - "Can it be done to change a cell color using an if
statement or some other
type of formula and NOT use conditional formating." Answer - Yes with VBA.
VBA samples provided by Bob and myself. Item 4.
What is Case 1 and so on? - you type the number which you want to format
with a particular color after the word Case. In these examples we are saying
if you enter the number 1 in a cell color it red, if the number is 2 then
color it yellow and so on. The best way is to copy the
Case 1: .ColorIndex = 3
line down as many times as you need and change the number after the word
Case to the numbers you will be formatting.
The best way to determine the values you need for the ColorIndex is to turn
on the recorder and change a cell's color 12 times. Then stop the recorder.
Look at the code Excel has created there will be 12 different numbers. You
can manually put those number in the macro. Numbers run from 1 to 56 I
believe and have no relationship to the color. The code Bob and I suggested
should be put in the Sheet object in the VBE. This means choose Tools,
Macro, Visual Basic Editor. On the upper left side of the screen you will
see a window called the Project - VBAProject. In that window each workbook
you have open will be displayed. Click the + sign beside the workbook you
are in to display the various objects, - they may already be displayed. You
will see a Sheet listed for each sheet in your workbook. Double-click the one
that you want the formatting in. In the window on the right paste a copy of
our code in and modify it as desired.

The 'red
'yellow
and so on are not necessary, Bob added those so you would know what color
would probably be displayed if you used his values. You can take them out.


--
Cheers,
Shane Devenshire


"Shu of AZ" wrote:

Thanks Shane but I did not see the answer to my first question. Define what
Case 1, Case 2, Case 3.

Does this refer to what would be in my case,,, the number 1, the number 2
and so?
on.

Referencing your #2. I'm not sure what that mention does for me as I wrote
I have 12 different values and need 12 different colors.

Referencing your #3. I still work with 2003.

The Range is H70:H82
The values possible are 1 thru 12
The colors are White font over Red, Black font over White, White font over
Blue, Black font over Yellow, Yellow font over Green, Yellow font over Black,
Black font over Orange, Black font over Pink, Black font over Aqua, White
font over Purple, Red font over Gray, and Black font over Lime in the same
order as the numbers.


"ShaneDevenshire" wrote:

Hi,

1. First the answer to your last question - no it doesn't matter.
2. FYI you can format cells using a Format, Cells command but you are
limited to changing the color of the text and you are limited to a max of 4
colors.
3. In 2007 this can be handled with Conditional Formatting because the 3
conditions restriction in 2003 and earlier has been removed and the number of
condtions is limited only by memory.
4. You can simplify the code Bob sent to read:

Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error Resume Next
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target.Interior
Select Case Target
Case 1: .ColorIndex = 3 'red
Case 2: .ColorIndex = 6 'yellow
Case 3: .ColorIndex = 5 'blue
Case 4: .ColorIndex = 10 'green
End Select
End With
End If

End Sub

This works because formatting a cell does not trigger the Change event so
there is not need for Application.EnableEvent. Your code is shorter if you
use the With statement Target.Interior because you are going to repeat the
Interior property 12 times in your code and the Value property once. Further
since Value is the default property of a cell (target) you can omit it.
Finally since you don't need the Application.EnableEvent statements you can
change the error handler to On Error Resume Next.

I believe that all of these changes will still allow the code to perform
correctly.
--
Cheers,
Shane Devenshire


"Shu of AZ" wrote:

Thanks Bob, quick question. Where 'Select Case .value' is involved. If my
'case values' are numbers such as 1, 2, 3, up to 12, or blank. Would the
following code be;

Select Case .Value
1: .Interior.ColorIndex = 3
etc.

Also were you wrote Const WS_RANGE As String = "H1:H10"
In my sheet, there are 12 cells in one column that could all have the
possible value of anything between 1 and 12 or be blank. Does that matter?

Hence;
1
2
4
6
9
12
blank
blank
blank
blank
blank
blank





"Bob Phillips" wrote:

Here is a VBA example


'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
'etc.
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
__________________________________
HTH

Bob

"Shu of AZ" wrote in message
...
Can it be done to change a cell color using an if statement or some other
type of formula and NOT use conditional formating.

I have 12 different possible values that could populate a cell and would
like to change the cell to one of 12 different colors already associated
to
the value.

Thanks

Shu

Here is some sample code that uses arrays for the conditions and colors.

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1:A25")
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
vals = Array("C", "D", "G", "H", "K", "L", "O", "S", "C", "X")
nums = Array(8, 9, 6, 3, 7, 4, 20, 10, 8, 15)
For Each rr In r
icolor = 0
For I = LBound(vals) To UBound(vals)
If UCase(rr.Value) = vals(I) Then
icolor = nums(I)
End If
Next
If icolor 0 Then
rr.Interior.ColorIndex = icolor
End If
Next
End Sub


Gord Dibben MS Excel MVP

On Tue, 1 Jul 2008 07:53:01 -0700, Shu of AZ
wrote:

Thanks Shane and Bob,

The code works fantastic.

Although my foresight was lacking. Because of the color changes occuring, I
also have to be able to change the color of the font. MY ATTEMPT failed as
seen below in Case 1.
Any assistance in correcting the code line for font color would be
appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "o5:o43"

On Error Resume Next
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target.Interior
Select Case Target
Case 1: .ColorIndex = 1
Case 1: .Font.ColorIndex = 3 <====== MY ATTEMPT
Case 2: .ColorIndex = 6 'yellow
Case 3: .ColorIndex = 5 'blue
Case 4: .ColorIndex = 10 'green
End Select
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

"ShaneDevenshire" wrote:

Hi,

First question - "Can it be done to change a cell color using an if
statement or some other
type of formula and NOT use conditional formating." Answer - Yes with VBA.
VBA samples provided by Bob and myself. Item 4.
What is Case 1 and so on? - you type the number which you want to format
with a particular color after the word Case. In these examples we are saying
if you enter the number 1 in a cell color it red, if the number is 2 then
color it yellow and so on. The best way is to copy the
Case 1: .ColorIndex = 3
line down as many times as you need and change the number after the word
Case to the numbers you will be formatting.
The best way to determine the values you need for the ColorIndex is to turn
on the recorder and change a cell's color 12 times. Then stop the recorder.
Look at the code Excel has created there will be 12 different numbers. You
can manually put those number in the macro. Numbers run from 1 to 56 I
believe and have no relationship to the color. The code Bob and I suggested
should be put in the Sheet object in the VBE. This means choose Tools,
Macro, Visual Basic Editor. On the upper left side of the screen you will
see a window called the Project - VBAProject. In that window each workbook
you have open will be displayed. Click the + sign beside the workbook you
are in to display the various objects, - they may already be displayed. You
will see a Sheet listed for each sheet in your workbook. Double-click the one
that you want the formatting in. In the window on the right paste a copy of
our code in and modify it as desired.

The 'red
'yellow
and so on are not necessary, Bob added those so you would know what color
would probably be displayed if you used his values. You can take them out.


--
Cheers,
Shane Devenshire


"Shu of AZ" wrote:

Thanks Shane but I did not see the answer to my first question. Define what
Case 1, Case 2, Case 3.

Does this refer to what would be in my case,,, the number 1, the number 2
and so?
on.

Referencing your #2. I'm not sure what that mention does for me as I wrote
I have 12 different values and need 12 different colors.

Referencing your #3. I still work with 2003.

The Range is H70:H82
The values possible are 1 thru 12
The colors are White font over Red, Black font over White, White font over
Blue, Black font over Yellow, Yellow font over Green, Yellow font over Black,
Black font over Orange, Black font over Pink, Black font over Aqua, White
font over Purple, Red font over Gray, and Black font over Lime in the same
order as the numbers.


"ShaneDevenshire" wrote:

Hi,

1. First the answer to your last question - no it doesn't matter.
2. FYI you can format cells using a Format, Cells command but you are
limited to changing the color of the text and you are limited to a max of 4
colors.
3. In 2007 this can be handled with Conditional Formatting because the 3
conditions restriction in 2003 and earlier has been removed and the number of
condtions is limited only by memory.
4. You can simplify the code Bob sent to read:

Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error Resume Next
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target.Interior
Select Case Target
Case 1: .ColorIndex = 3 'red
Case 2: .ColorIndex = 6 'yellow
Case 3: .ColorIndex = 5 'blue
Case 4: .ColorIndex = 10 'green
End Select
End With
End If

End Sub

This works because formatting a cell does not trigger the Change event so
there is not need for Application.EnableEvent. Your code is shorter if you
use the With statement Target.Interior because you are going to repeat the
Interior property 12 times in your code and the Value property once. Further
since Value is the default property of a cell (target) you can omit it.
Finally since you don't need the Application.EnableEvent statements you can
change the error handler to On Error Resume Next.

I believe that all of these changes will still allow the code to perform
correctly.
--
Cheers,
Shane Devenshire


"Shu of AZ" wrote:

Thanks Bob, quick question. Where 'Select Case .value' is involved. If my
'case values' are numbers such as 1, 2, 3, up to 12, or blank. Would the
following code be;

Select Case .Value
1: .Interior.ColorIndex = 3
etc.

Also were you wrote Const WS_RANGE As String = "H1:H10"
In my sheet, there are 12 cells in one column that could all have the
possible value of anything between 1 and 12 or be blank. Does that matter?

Hence;
1
2
4
6
9
12
blank
blank
blank
blank
blank
blank





"Bob Phillips" wrote:

Here is a VBA example


'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
'etc.
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
__________________________________
HTH

Bob

"Shu of AZ" wrote in message
...
Can it be done to change a cell color using an if statement or some other
type of formula and NOT use conditional formating.

I have 12 different possible values that could populate a cell and would
like to change the cell to one of 12 different colors already associated
to
the value.

Thanks

So does that mean that each sheet has a different set of colours or they all
have the same criteria-colour combinations?

--
__________________________________
HTH

Bob

"Shu of AZ" wrote in message
...
Will this code you have provided me, work for each of the twelve sheets or
do
I have to activate each sheet, then have the block of code, then activate
another sheet then the block of code and so on.

In the workbook, there are 12 sheets and each sheet is identical except
the
name of the sheet, ( R1, R2, up to R12 ) The data loads into each sheet
and
onto the same range but is entirely different on each sheet. I need to
have
the code recognize which sheet it is on so the colors change according to
the
Case # on each sheet.

"Bob Phillips" wrote:

Can you clarify exactly what this means ... The code, will that work for
all
sheets as each sheet has a different set of data in that range and needs
to
indicate the range value by a colour.


--
__________________________________
HTH

Bob

"Shu of AZ" wrote in message
...
another question if you don't mind. I have 12 sheets, named R1, R2, R3
etc.
Each sheet is actually a duplicate of the others but named differently
as
you
can see in the R1, R2 and so on. The code, will that work for all
sheets
as
each sheet has a different set of data in that range and needs to
indicate
the range value by a color.

"ShaneDevenshire" wrote:

Hi,

First question - "Can it be done to change a cell color using an if
statement or some other
type of formula and NOT use conditional formating." Answer - Yes with
VBA.
VBA samples provided by Bob and myself. Item 4.
What is Case 1 and so on? - you type the number which you want to
format
with a particular color after the word Case. In these examples we are
saying
if you enter the number 1 in a cell color it red, if the number is 2
then
color it yellow and so on. The best way is to copy the
Case 1: .ColorIndex = 3
line down as many times as you need and change the number after the
word
Case to the numbers you will be formatting.
The best way to determine the values you need for the ColorIndex is to
turn
on the recorder and change a cell's color 12 times. Then stop the
recorder.
Look at the code Excel has created there will be 12 different numbers.
You
can manually put those number in the macro. Numbers run from 1 to 56
I
believe and have no relationship to the color. The code Bob and I
suggested
should be put in the Sheet object in the VBE. This means choose
Tools,
Macro, Visual Basic Editor. On the upper left side of the screen you
will
see a window called the Project - VBAProject. In that window each
workbook
you have open will be displayed. Click the + sign beside the workbook
you
are in to display the various objects, - they may already be
displayed.
You
will see a Sheet listed for each sheet in your workbook. Double-click
the
one
that you want the formatting in. In the window on the right paste a
copy
of
our code in and modify it as desired.

The 'red
'yellow
and so on are not necessary, Bob added those so you would know what
color
would probably be displayed if you used his values. You can take them
out.


--
Cheers,
Shane Devenshire


"Shu of AZ" wrote:

Thanks Shane but I did not see the answer to my first question.
Define
what
Case 1, Case 2, Case 3.

Does this refer to what would be in my case,,, the number 1, the
number
2
and so?
on.

Referencing your #2. I'm not sure what that mention does for me as
I
wrote
I have 12 different values and need 12 different colors.

Referencing your #3. I still work with 2003.

The Range is H70:H82
The values possible are 1 thru 12
The colors are White font over Red, Black font over White, White
font
over
Blue, Black font over Yellow, Yellow font over Green, Yellow font
over
Black,
Black font over Orange, Black font over Pink, Black font over Aqua,
White
font over Purple, Red font over Gray, and Black font over Lime in
the
same
order as the numbers.


"ShaneDevenshire" wrote:

Hi,

1. First the answer to your last question - no it doesn't matter.
2. FYI you can format cells using a Format, Cells command but you
are
limited to changing the color of the text and you are limited to a
max of 4
colors.
3. In 2007 this can be handled with Conditional Formatting because
the 3
conditions restriction in 2003 and earlier has been removed and
the
number of
condtions is limited only by memory.
4. You can simplify the code Bob sent to read:

Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error Resume Next
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target.Interior
Select Case Target
Case 1: .ColorIndex = 3 'red
Case 2: .ColorIndex = 6 'yellow
Case 3: .ColorIndex = 5 'blue
Case 4: .ColorIndex = 10 'green
End Select
End With
End If

End Sub

This works because formatting a cell does not trigger the Change
event so
there is not need for Application.EnableEvent. Your code is
shorter
if you
use the With statement Target.Interior because you are going to
repeat the
Interior property 12 times in your code and the Value property
once.
Further
since Value is the default property of a cell (target) you can
omit
it.
Finally since you don't need the Application.EnableEvent
statements
you can
change the error handler to On Error Resume Next.

I believe that all of these changes will still allow the code to
perform
correctly.
--
Cheers,
Shane Devenshire


"Shu of AZ" wrote:

Thanks Bob, quick question. Where 'Select Case .value' is
involved. If my
'case values' are numbers such as 1, 2, 3, up to 12, or blank.
Would the
following code be;

Select Case .Value
1: .Interior.ColorIndex = 3
etc.

Also were you wrote Const WS_RANGE As String = "H1:H10"
In my sheet, there are 12 cells in one column that could all
have
the
possible value of anything between 1 and 12 or be blank. Does
that
matter?

Hence;
1
2
4
6
9
12
blank
blank
blank
blank
blank
blank





"Bob Phillips" wrote:

Here is a VBA example


'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing
Then
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
'etc.
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a
standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
__________________________________
HTH

Bob

"Shu of AZ" wrote in
message
...
Can it be done to change a cell color using an if statement
or
some other
type of formula and NOT use conditional formating.

I have 12 different possible values that could populate a
cell
and would
like to change the cell to one of 12 different colors
already
associated
to
the value.

Thanks

Thanks Gord but this is beyond my capabilities in translating. I have
started another thread today, "Continued Post - ...."
I've made a couple of attempts in coding using Select Case but to no avail.
I'm going to post another comment of mine on what I tried.

The problem with the event code Shane and Bob gave me is that the enter key
must be pressed. On my sheet, the data is coming from another sheet and is
populated by a Vlookup. So, nothing happens. If I go to the first cell in
the range, and type in a number, which removes the formula then press enter
it works but that isn't workable in my sheet. Please look at the new thread
for me. Appreciate your time.



"Gord Dibben" wrote:

Shu

Here is some sample code that uses arrays for the conditions and colors.

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1:A25")
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
vals = Array("C", "D", "G", "H", "K", "L", "O", "S", "C", "X")
nums = Array(8, 9, 6, 3, 7, 4, 20, 10, 8, 15)
For Each rr In r
icolor = 0
For I = LBound(vals) To UBound(vals)
If UCase(rr.Value) = vals(I) Then
icolor = nums(I)
End If
Next
If icolor 0 Then
rr.Interior.ColorIndex = icolor
End If
Next
End Sub


Gord Dibben MS Excel MVP

On Tue, 1 Jul 2008 07:53:01 -0700, Shu of AZ
wrote:

Thanks Shane and Bob,

The code works fantastic.

Although my foresight was lacking. Because of the color changes occuring, I
also have to be able to change the color of the font. MY ATTEMPT failed as
seen below in Case 1.
Any assistance in correcting the code line for font color would be
appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "o5:o43"

On Error Resume Next
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target.Interior
Select Case Target
Case 1: .ColorIndex = 1
Case 1: .Font.ColorIndex = 3 <====== MY ATTEMPT
Case 2: .ColorIndex = 6 'yellow
Case 3: .ColorIndex = 5 'blue
Case 4: .ColorIndex = 10 'green
End Select
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

"ShaneDevenshire" wrote:

Hi,

First question - "Can it be done to change a cell color using an if
statement or some other
type of formula and NOT use conditional formating." Answer - Yes with VBA.
VBA samples provided by Bob and myself. Item 4.
What is Case 1 and so on? - you type the number which you want to format
with a particular color after the word Case. In these examples we are saying
if you enter the number 1 in a cell color it red, if the number is 2 then
color it yellow and so on. The best way is to copy the
Case 1: .ColorIndex = 3
line down as many times as you need and change the number after the word
Case to the numbers you will be formatting.
The best way to determine the values you need for the ColorIndex is to turn
on the recorder and change a cell's color 12 times. Then stop the recorder.
Look at the code Excel has created there will be 12 different numbers. You
can manually put those number in the macro. Numbers run from 1 to 56 I
believe and have no relationship to the color. The code Bob and I suggested
should be put in the Sheet object in the VBE. This means choose Tools,
Macro, Visual Basic Editor. On the upper left side of the screen you will
see a window called the Project - VBAProject. In that window each workbook
you have open will be displayed. Click the + sign beside the workbook you
are in to display the various objects, - they may already be displayed. You
will see a Sheet listed for each sheet in your workbook. Double-click the one
that you want the formatting in. In the window on the right paste a copy of
our code in and modify it as desired.

The 'red
'yellow
and so on are not necessary, Bob added those so you would know what color
would probably be displayed if you used his values. You can take them out.


--
Cheers,
Shane Devenshire


"Shu of AZ" wrote:

Thanks Shane but I did not see the answer to my first question. Define what
Case 1, Case 2, Case 3.

Does this refer to what would be in my case,,, the number 1, the number 2
and so?
on.

Referencing your #2. I'm not sure what that mention does for me as I wrote
I have 12 different values and need 12 different colors.

Referencing your #3. I still work with 2003.

The Range is H70:H82
The values possible are 1 thru 12
The colors are White font over Red, Black font over White, White font over
Blue, Black font over Yellow, Yellow font over Green, Yellow font over Black,
Black font over Orange, Black font over Pink, Black font over Aqua, White
font over Purple, Red font over Gray, and Black font over Lime in the same
order as the numbers.


"ShaneDevenshire" wrote:

Hi,

1. First the answer to your last question - no it doesn't matter.
2. FYI you can format cells using a Format, Cells command but you are
limited to changing the color of the text and you are limited to a max of 4
colors.
3. In 2007 this can be handled with Conditional Formatting because the 3
conditions restriction in 2003 and earlier has been removed and the number of
condtions is limited only by memory.
4. You can simplify the code Bob sent to read:

Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error Resume Next
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target.Interior
Select Case Target
Case 1: .ColorIndex = 3 'red
Case 2: .ColorIndex = 6 'yellow
Case 3: .ColorIndex = 5 'blue
Case 4: .ColorIndex = 10 'green
End Select
End With
End If

End Sub

This works because formatting a cell does not trigger the Change event so
there is not need for Application.EnableEvent. Your code is shorter if you
use the With statement Target.Interior because you are going to repeat the
Interior property 12 times in your code and the Value property once. Further
since Value is the default property of a cell (target) you can omit it.
Finally since you don't need the Application.EnableEvent statements you can
change the error handler to On Error Resume Next.

I believe that all of these changes will still allow the code to perform
correctly.
--
Cheers,
Shane Devenshire


"Shu of AZ" wrote:

Thanks Bob, quick question. Where 'Select Case .value' is involved. If my
'case values' are numbers such as 1, 2, 3, up to 12, or blank. Would the
following code be;

Select Case .Value
1: .Interior.ColorIndex = 3
etc.

Also were you wrote Const WS_RANGE As String = "H1:H10"
In my sheet, there are 12 cells in one column that could all have the
possible value of anything between 1 and 12 or be blank. Does that matter?

Hence;
1
2
4
6
9
12
blank
blank
blank
blank
blank
blank





"Bob Phillips" wrote:

Here is a VBA example


'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
'etc.
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
__________________________________
HTH

Bob

"Shu of AZ" wrote in message
...
Can it be done to change a cell color using an if statement or some other
type of formula and NOT use conditional formating.

I have 12 different possible values that could populate a cell and would
like to change the cell to one of 12 different colors already associated
to
the value.

Thanks

Yes they all meet the same color/number(case) criteria. What is the only
difference is that each sheet/range may get different numbers in that range
yet they will always be between 1 and 12 with blanks if there is not 12
numbers to fill up the range completely. Bob, I started another post called,
CONTINUED POST today because I feared a different day would be difficult to
maintain the post. I even have tried to place code in the main macro using
Select case but to no avail. The problem with the event macro is one would
have to press the enter key to get the event to take place. All of the cells
in the range I'm dealing with get the data from a Vlookup from another sheet
that downloads all the data used in all the sheets. So when that happens,
nothing happens. Thanks for your time.

"Bob Phillips" wrote:

So does that mean that each sheet has a different set of colours or they all
have the same criteria-colour combinations?

--
__________________________________
HTH

Bob

"Shu of AZ" wrote in message
...
Will this code you have provided me, work for each of the twelve sheets or
do
I have to activate each sheet, then have the block of code, then activate
another sheet then the block of code and so on.

In the workbook, there are 12 sheets and each sheet is identical except
the
name of the sheet, ( R1, R2, up to R12 ) The data loads into each sheet
and
onto the same range but is entirely different on each sheet. I need to
have
the code recognize which sheet it is on so the colors change according to
the
Case # on each sheet.

"Bob Phillips" wrote:

Can you clarify exactly what this means ... The code, will that work for
all
sheets as each sheet has a different set of data in that range and needs
to
indicate the range value by a colour.


--
__________________________________
HTH

Bob

"Shu of AZ" wrote in message
...
another question if you don't mind. I have 12 sheets, named R1, R2, R3
etc.
Each sheet is actually a duplicate of the others but named differently
as
you
can see in the R1, R2 and so on. The code, will that work for all
sheets
as
each sheet has a different set of data in that range and needs to
indicate
the range value by a color.

"ShaneDevenshire" wrote:

Hi,

First question - "Can it be done to change a cell color using an if
statement or some other
type of formula and NOT use conditional formating." Answer - Yes with
VBA.
VBA samples provided by Bob and myself. Item 4.
What is Case 1 and so on? - you type the number which you want to
format
with a particular color after the word Case. In these examples we are
saying
if you enter the number 1 in a cell color it red, if the number is 2
then
color it yellow and so on. The best way is to copy the
Case 1: .ColorIndex = 3
line down as many times as you need and change the number after the
word
Case to the numbers you will be formatting.
The best way to determine the values you need for the ColorIndex is to
turn
on the recorder and change a cell's color 12 times. Then stop the
recorder.
Look at the code Excel has created there will be 12 different numbers.
You
can manually put those number in the macro. Numbers run from 1 to 56
I
believe and have no relationship to the color. The code Bob and I
suggested
should be put in the Sheet object in the VBE. This means choose
Tools,
Macro, Visual Basic Editor. On the upper left side of the screen you
will
see a window called the Project - VBAProject. In that window each
workbook
you have open will be displayed. Click the + sign beside the workbook
you
are in to display the various objects, - they may already be
displayed.
You
will see a Sheet listed for each sheet in your workbook. Double-click
the
one
that you want the formatting in. In the window on the right paste a
copy
of
our code in and modify it as desired.

The 'red
'yellow
and so on are not necessary, Bob added those so you would know what
color
would probably be displayed if you used his values. You can take them
out.


--
Cheers,
Shane Devenshire


"Shu of AZ" wrote:

Thanks Shane but I did not see the answer to my first question.
Define
what
Case 1, Case 2, Case 3.

Does this refer to what would be in my case,,, the number 1, the
number
2
and so?
on.

Referencing your #2. I'm not sure what that mention does for me as
I
wrote
I have 12 different values and need 12 different colors.

Referencing your #3. I still work with 2003.

The Range is H70:H82
The values possible are 1 thru 12
The colors are White font over Red, Black font over White, White
font
over
Blue, Black font over Yellow, Yellow font over Green, Yellow font
over
Black,
Black font over Orange, Black font over Pink, Black font over Aqua,
White
font over Purple, Red font over Gray, and Black font over Lime in
the
same
order as the numbers.


"ShaneDevenshire" wrote:

Hi,

1. First the answer to your last question - no it doesn't matter.
2. FYI you can format cells using a Format, Cells command but you
are
limited to changing the color of the text and you are limited to a
max of 4
colors.
3. In 2007 this can be handled with Conditional Formatting because
the 3
conditions restriction in 2003 and earlier has been removed and
the
number of
condtions is limited only by memory.
4. You can simplify the code Bob sent to read:

Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error Resume Next
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target.Interior
Select Case Target
Case 1: .ColorIndex = 3 'red
Case 2: .ColorIndex = 6 'yellow
Case 3: .ColorIndex = 5 'blue
Case 4: .ColorIndex = 10 'green
End Select
End With
End If

End Sub

This works because formatting a cell does not trigger the Change
event so
there is not need for Application.EnableEvent. Your code is
shorter
if you
use the With statement Target.Interior because you are going to
repeat the
Interior property 12 times in your code and the Value property
once.
Further
since Value is the default property of a cell (target) you can
omit
it.
Finally since you don't need the Application.EnableEvent
statements
you can
change the error handler to On Error Resume Next.

I believe that all of these changes will still allow the code to
perform
correctly.
--
Cheers,
Shane Devenshire


"Shu of AZ" wrote:

Thanks Bob, quick question. Where 'Select Case .value' is
involved. If my
'case values' are numbers such as 1, 2, 3, up to 12, or blank.
Would the
following code be;

Select Case .Value
1: .Interior.ColorIndex = 3
etc.

Also were you wrote Const WS_RANGE As String = "H1:H10"
In my sheet, there are 12 cells in one column that could all
have
the
possible value of anything between 1 and 12 or be blank. Does
that
matter?

Hence;
1
2
4
6
9
12
blank
blank
blank
blank
blank
blank





"Bob Phillips" wrote:

Here is a VBA example


'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing
Then
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
'etc.
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a
standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
__________________________________
HTH

Bob

"Shu of AZ" wrote in
message
...
Can it be done to change a cell color using an if statement
or