Conditional Formatting

[gresple]

Can I have more than 3 conditions for the conditional formatting function?

[Aladin Akyurek]

On 2002-04-24 11:11, gresple wrote:
Can I have more than 3 conditions for the conditional formatting function?

No. There is some VBA code at this site that allows for more conditions. Try to use Search facility of the site if interested.

[Mark W.]

On 2002-04-24 11:11, gresple wrote:
Can I have more than 3 conditions for the conditional formatting function?

You can have a compound condition involving more than 3 conditions that apply a single format. So, yes, strictly speaking you can have more 3 conditions, but you're constrained by number of formats (3) that can be applied with Excel's Conditional Formatting menu command.

[ This Message was edited by: Mark W. on 2002-04-24 11:23 ]

[lenze]

Actually, depending on the type of conditions you have, you may be able to trick excel into giving you more formats by combining custom formatting with conditional formatting. As an example

Say you want the following formatting for a range of cells

Value Color
<5 Black
5-9 Green
10-14 Blue
15-19 Cyan
20-24 Purple
25+ Red

Select your range and Under format, choose custom and enter [<5][Black]0;[<10][Green]0;[<15][Blue]0

Now under conditional formatting enter

Condition 1 Cell Value >=25 Format Red
Condition 2 Cell Value >=20 Format Purple
Condition 3 Cell Value >=15 Format Cyan

This will give you 6 conditional formats for your range.

[fb250r]

It's not working. It does not like the formula.

[Tom Urtis]

You can do this with VBA as Aladin, Mark, and lenze suggested. Here is a simple example, although your question was not clear as to whether your data is numeric, text, dates, etc. This code uses a select case structure for cell A1's interior color index (cell shading), depending on what number is entered into A1. You can expand the conditions as needed.

Right click on your sheet tab, left click on View Code, and paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Or Target.Cells.Count > 1 Then Exit Sub
Select Case [A1].Value
Case 1
Target.Interior.ColorIndex = 3
Case 2
Target.Interior.ColorIndex = 6
Case 3
Target.Interior.ColorIndex = 8
Case 4
Target.Interior.ColorIndex = 22
Case 5
Target.Interior.ColorIndex = 25
Case Else
Target.Interior.ColorIndex = 15
End Select
End Sub

Is this any help for what you were requesting?

[billyboyuk2000]

Right the VBA code works a treat..thanks for that.

Instead of keying in "SB" = colour on the VBA code could it be linked to cell references so you opnly have to change the "SB" to a "BA" and it'll still change colour?

Hope that makes sense?

[ballbugsbunny]

I need to be able to colour a row depending on the value of the element in the first column of the row. I need 4 different colours and with excel's capabilities i can only have 3 colours. So i need to have a vb part. It needs to be dynamic though.. Values are numeric and text strings. Can you inform me about the function that colours the whole row? and something else.. Inside the case function what is the dynamic declaration of the row? It my first time with excel..
Thanx

[Ian Betteridge]

Hi Lenze,
Just reading your reply, can you tell me where the custom is on format.
You wrote:
Select your range and Under format, choose custom and enter

Thanks Ian

[lenze]

Hi:

It's under Format>Cells on the Number Tab. It's one of the options in the category list. When you select it, enter the formula in the Type field.

lenze