Conditional Formatting

gresple

New Member
Joined
Apr 23, 2002
Messages
5
Can I have more than 3 conditions for the conditional formatting function?
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
following on from formatting question

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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,583
Messages
6,114,506
Members
448,575
Latest member
hycrow

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