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