Multiple Conditional Format

  • Thread starter Thread starter Duncs
  • Start date Start date
D

Duncs

Is it possible to have more than three conditional formats set, in the
"Conditional Formatting" window? If so, how do you do it?

Or, is this the limit and that's that?

TIA

Duncs
 
Toppers,

Unfortunately, our organisation has "Locked Down" Excel, and so the
Tools menu is not available to me.

Is there another way to achieve the same thing?

TIA

Duncs
 
If you are allowed to use VBA macros, then this is the only other way:

To install, click on worksheet tab where CF is required , "View code" and
copy/paste code below which will (obviously) have to be modified to suit your
requirements.

..
'-----------------------------------------------------------------
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
 
Duncs said:
Is it possible to have more than three conditional formats set,
in the "Conditional Formatting" window? If so, how do you do it?
Click to expand...

You actually have four: three in conditionals, and one more you
can set as the default for the range.

Three is the limit until Excel 2007 (I believe). With the 2007
version, assuming the info I've just stated is correct,[1], you
have many more.

However, you can combine formulas in tricky ways using AND and
OR, IF-statements, etc. For example, I have a stock symbol
column in a chart that uses conditional formatting to color
every other row. But now I want to outline and have a different
font color for the symbols that represent options, not stocks.
(They are almost always more than 4 characters in length,
whereas the normal listed and NASDAQ issues I trade are virtually
always limited to four chars, max.)

So I have as condition 1 in that column, as a formula:
=AND(MOD(ROW(),2),LEN(OFFSET($E$1,ROW()-1,))>4)

And the formatting is the fill color for my odd-numbered rows,
along with the "watermelon pink/red" color for borders and font
for those option symbols.


Condition 2:
=LEN(OFFSET($E$1,ROW()-1,))>4

This condition handles the options symbols for the even-numbered
rows with no background fill. The font color and borders are set
as above, but the background fill is left off.

(Remember that the first condition that evaluates to True is
applied, and then Excel exits the condtional module.)

Condition 3:
=MOD(ROW(),2)

This applies only my background fill to color in the odd-numbered
row-cells, but leaves the other formatting alone as for normal
stock symbols.

If you want to see the result, it's currently on a web page at
http://heliotropos.com/xl/tmp/OpenOrders.jpg
and is Column E.

Btw, all the colors you see there other than the default font
and fill are done with conditionals.

A final note: there is a free add-in to extend the conditional
formatting dramatically. I have used it a couple of times, but
found it was a performance drag on my already top-heavy worksheets
(lots of array formulas, etc.), so took it back out for now.
But it certainly does seem slick and can fill a need. It also
can apply multiple conditions, not just the first one found that
evaluates to True.

I can't remember which one I downloaded and tried, as Google
tells me there seem to be several. Here is what I just
found, but I have no opinion positive or negative about it.
It may or may not be the one I tried last year.

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

I see there are a number of others that are shareware, not freeware.

See also, e.g.,
http://www.mvps.org/dmcritchie/excel/condfmt.htm


[1] I use 2002.
 
As Toppers said, without either Excel 2007 or access to VBA/Macros, 3
conditionals are all you are allowd (with 4th default condition as Dallman
Ross noted). Even moving to 2007 may not be the solution IF you are the only
one with 2007 and other people need to use your files. While there are
add-ins from Microsoft for earlier versions of Excel to use to view/use 2007
files, you're going to lose appearance/functionality when someone on another
machine saves it as an earlier version of Excel format.

Perhaps someone needs to review the organization's policy on Tools use. I'm
curious, if you press [Alt]+[F11] what happens? Also, do you know if Macro
Security has been set to High (meaning Only signed macros from trusted
sources will be allowed to run, unsigned macros are automatically disabled),
or Very High: Only macros installed in trusted locations will be allowed to
run. All other signed and unsigned macros are disabled.

Realizing you have to use Tools to get to view the Security level settings,
you may have to ask 'the powers that be' just how macros are handled with
regards to security.

Since the code needed to provide you with multiple conditional formatting
would be attached to the Worksheet_Change event, it may be that you can
somehow make arrangements with those 'powers that be' to enable the feature.
Where there's a will, there's usually a way (or a bunch of greedy relatives).
 
Toppers,

Many thanks for this. It works a treat, and gives me something that I
can work on / use as a basis for my needs.

Many thanks

Duncs
 
It all seems strange.

I can press ALT+F11 to access VBA with no problem. When I open a
spreadsheet with macros / VBA code in it, I get the "Security Warning"
window advising of the existence of macros, and the three buttons at
the bottom. Selecting "Enable" allows me to open the file, and use it
as it was intended.

I've contacted our support team, who have no idea why the Tools menu
is disabled. Go figure!

Duncs

As Toppers said, without either Excel 2007 or access to VBA/Macros, 3
conditionals are all you are allowd (with 4th default condition as Dallman
Ross noted). Even moving to 2007 may not be the solution IF you are the only
one with 2007 and other people need to use your files. While there are
add-ins from Microsoft for earlier versions of Excel to use to view/use 2007
files, you're going to lose appearance/functionality when someone on another
machine saves it as an earlier version of Excel format.

Perhaps someone needs to review the organization's policy on Tools use. I'm
curious, if you press [Alt]+[F11] what happens? Also, do you know if Macro
Security has been set to High (meaning Only signed macros from trusted
sources will be allowed to run, unsigned macros are automatically disabled),
or Very High: Only macros installed in trusted locations will be allowed to
run. All other signed and unsigned macros are disabled.

Realizing you have to use Tools to get to view the Security level settings,
you may have to ask 'the powers that be' just how macros are handled with
regards to security.

Since the code needed to provide you with multiple conditional formatting
would be attached to the Worksheet_Change event, it may be that you can
somehow make arrangements with those 'powers that be' to enable the feature.
Where there's a will, there's usually a way (or a bunch of greedy relatives).



Duncs said:
Is it possible to have more than three conditional formats set, in the
"Conditional Formatting" window? If so, how do you do it?
Click to expand...
Or, is this the limit and that's that?

Duncs- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
 
Back
Top