If statement and colors

[Shu of AZ]

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

 

[Mike]

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

 

[Pete_UK]

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

 

[Bob Phillips]

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.

 

[Pete_UK]

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

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

Hope this helps.

Pete

 

[Shu of AZ]

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

 

[ShaneDevenshire]

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.

 

[Shu of AZ]

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]

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.

 

[Shu of AZ]

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

 

[Shu of AZ]

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.

 

[Bob Phillips]

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.

 

[Shu of AZ]

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.

 

[Shu of AZ]

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 = "o543"

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

 

[Gord Dibben]

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

 

[Bob Phillips]

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

 

[Shu of AZ]

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.

 

[Shu of AZ]

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.