Question about conditional formatting

Jim_vande_Berg

New Member
Joined
Jan 25, 2003
Messages
2
Hello,

With the option Conditional format it is very easy to give cells a different
format based on their data, however it is only possible to set three conditions.
Is there an easy way to e.g. create 5 different formats (in my case 5 different colors) based on data in one column?

The other way around: is it also possible to do some action on cells which have specific colors (e.g. if Red, than count these cells)? is this possible via a standard function within Excel or do I need to use VBA for achieving this.

Thanks!
Jim van de Berg
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
I feel that the "easiest" way to deal with both problems is to use VBA.

Heres a quick example of VBA code to change cell color relative to cell value.

Private Sub Worksheet_Change(ByVal Target As Range)
With Target.Interior

Select Case Target.Value


Case 1
.ColorIndex = 6
.Pattern = xlSolid

Case 20 To 30
.ColorIndex = 3
.Pattern = xlSolid

Case Else
.ColorIndex = 0
.Pattern = xlSolid

End Select
End With
End Sub

EXPLAINATION:
if cell value = 1 then make cell yellow
if cell value = 20-30 then make red
For any other value don't use color

Note: to use code just :
1) rite click on Tab of Sheet you want to use
2) left click on "view code"
3) paste code into the VBA editor "Code window"
4) boomba ... done

VBA ADVANTAGE: no limit to how many conditions are tested for !

MORE INFO ON "SELECT-CASE"
example from VBA help file:

Select Case Statement Example
This example uses the Select Case statement to evaluate the value of a variable. The second Case clause contains the value of the variable being evaluated, and therefore only the statement associated with it is executed.

Dim Number
Number = 8 ' Initialize variable.
Select Case Number ' Evaluate Number.
Case 1 To 5 ' Number between 1 and 5, inclusive.
Debug.Print "Between 1 and 5"
' The following is the only Case clause that evaluates to True.
Case 6, 7, 8 ' Number between 6 and 8.
Debug.Print "Between 6 and 8"
Case 9 To 10 ' Number is 9 or 10.
Debug.Print "Greater than 8"
Case Else ' Other values.
Debug.Print "Not between 1 and 10"
End Select




_________________
<font size=-1>NOTE:</font.<font size=-1> (Testing performed on Win2K utilizing Office 2000. Solutions may need tweaking for other versions.)</font><font size=+1><font color="blue">Adieu,<font color="red">N<font color="blue">imrod</font
This message was edited by Nimrod on 2003-01-26 13:38
This message was edited by Nimrod on 2003-01-26 13:42
This message was edited by Nimrod on 2003-01-26 13:51
 
Upvote 0
You can get up to 6 font colours with a combination of Conditional Formatting (3) and Format/Cells/Number/Custom:

For example:<pre>< 0 Red
0 - 10 Green
11 - 20 Blue
21 - 30 Orange
31 - 40 Light Purple
+ 40 Black</pre>

Use Custom Formatting for< 0, 0 - 10 and + 40:

[Red][<0](#,##0.00);[Green][<10]0.00_);#,##0.00_);@

Use Conditional Formatting for:

Cell value between 11 - 20
Cell value between 21 - 30
Cell value between 31 - 40

You can use the SUMPRODUCT function to count the number of cells with a specific value(s). This function does not count colours as such but can be used to count the values in the cell e.g. count all cells in the range named DataRange that are > 0 and less than or equal to 31:

=SUMPRODUCT(((B44:B49)<=31)*((B44:B49)>0))


HTH

Mike
This message was edited by Ekim on 2003-01-27 00:45
 
Upvote 0
How do you change the format of a range of cells when using your Case statement?

I usually have formulas in my Conditional Format that set the entire row based on a value change from one column.

For example:

I use a formula like this: =FIND("T:",$B2,1)=1

It will then set the entire range (A2:F2) to a color I specify.


How do I tell VBA to use the range?
 
Upvote 0
This may have been answered above.... but I couldn't really tell.

I need to copy cells that are red to another worksheet. If they are not red, no copy. Is there anyway to do that?

Thank you,

Matt
 
Upvote 0
Hi everybody !

In the following table i need to set conditional formatting in C9:
if cell value = D5 (color in red)
if cell value = E5 ( color in blue)
if cell value = F5 (color in yellow)
if cell value = G5 (color in orange)
if cell value = H5 (color in teal)

which means more than 3 criteria.

I tried all the answers given in this thread and others and couldnt solve , is it possible ???

Thank you !
Book2
BCDEFGHI
1
2
3RANK12345
475431
5TYPEEBCAD
6
7
8TYPE
9A3
10B5
11C4
12D1
13E7
14
15
Sheet1
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top