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