stupid 3 limit on conditional formatting

[ken]

all i want to do is compare 1 cell to 6 others and if that number matches
any one of the six, i want it to change color. but noooooooooo i am only
allowed 3 conditions per cell. why would they put a limit on that????

 

[Harlan Grove]

all i want to do is compare 1 cell to 6 others and if that number matches
any one of the six, i want it to change color. but noooooooooo i am only
allowed 3 conditions per cell. why would they put a limit on that????

Why did God and/or evolution limit you to 2 arms and 2 legs? The creator
gets to set the specs and impose whatever limits s/he wants. You don't like
it? The full source code to Gnumeric and OpenOffice is available. Use them
to roll your own spreadsheet program.

Seriously, functionality takes memory. Excel already eats tons of it. The
Microsoft designers must have decided 3 (4 - include the case that none of
the conditions are satisfied) were enough of a memory drain since most cells
in most workbooks won't have any.

 

[Andy Brown]

Harlan answered the question you asked, I'll try to answer the one you
perhaps should have asked.

You could use event code. Assuming the one cell is A1, in B1 use
=IF(ISERROR(VLOOKUP(A1,$D$1:$E$3,2,FALSE)),0,VLOOKUP(A1,$D$1:$E$3,2,FALSE))

Use a lookup table in D1:E3, eg: 1, 3 ; 2, 5 ; 3, 7.

Right-click the sheet tab, select View Code. Paste the following into the
worksheet module -

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$A$1" Then
Target.Interior.ColorIndex = Range("B1").Value
End If
End Sub

This'll change A1 colour when its value becomes 1, 2 or 3. For more colours,
extend the lookup table (use Selection.Interior.ColorIndex = Selection.Value
to work out which whole numbers generate which colours).

HTH,
Andy

 

[Ken Wright]

So with your target cell that you want to change colour being F9, and your comparison cells being
F3,H3,J3,F5,H5,J5, why not something like:-

Change 'Cell Value is' to 'Formula is' and

=OR($F$9=$F$3,$F$9=$H$3,$F$9=$J$3,$F$9=$F$5,$F$9=$H$5,$F$9=$J$5)

That gives you 30 options for individual comparisons

 

[David McRitchie]

Hi Ken,
See it this helps. The actual macro supplied is going to color-
code which column of columns J through O that the formula
in column P matches. .

P2: =MIN(J2,K2,L2,M2,N2,O2)

See the Calculate Event macro in
Worksheet Events and Workbook Events
http://www.mvps.org/dmcritchie/excel/event.htm#calculate

directions to install an event macro differ from othe macros, to install
right-click on the sheettab, choose 'view code', and then paste in your macro.

If you need more help with changing the macro, you will have to be
more specific in your question. It always seems like a homework
question when the same question gets asked in two days with most of
the same details and some detail or part of the question is missing.

I suspect the limit is based on use of 2 bits to indicate the number
of Conditional Formats. 0, 1, 2, or 3. Logical not necessarily stupid.


HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

 

[ken]

Harlan answered the question you asked, I'll try to answer the one
you perhaps should have asked.

sorry for being a sarcastic- it was real late and nothing was going my
way. you are right, i should have just asked about how to do this instead
of why i cant:/ thanks for the help ill try and let you know how it works
out

 

[ken]

here is what im trying to do: my mom plays the lottery alot and im trying
to be a good son and make a sheet so that she can enter her numbers in a8
b8 c8 d8 e8 f8 and then compare each of those cells to the actual numbers
drawn in a5 b5 c5 d5 e5 f5 each of her numbers must be compared to each of
the actual numbers because they dont have to be in the exact order, this
way she can see if she gets 3 of 6 and win 7 bucks or whatever.

 

[Biff]

Hi Ken,

Use this for your conditional format:

=OR(A8=A5:F5)

Use this formula to count any numbers matched:

Array entered:

=SUM(COUNTIF(A8:F8,A5:F5))

Biff

 

[Anders S]

Ken,

You can use conditional formatting with the formula

=MATCH(A8,$A$5:$F$5,0)

HTH
Anders Silvén

 

[ken]

It works!!! thank you very very much!!

 

[Debra Dalgleish]

You're welcome. Thanks for reporting back.

It works!!! thank you very very much!!

 

[Ken Wright]

You do know that 'Talking to yourself' is the first sign of madness don't you? And you also
really shouldn't be so hard on yourself - Just keeping taking the tablets and you'll be OK.

 

[Harlan Grove]

So that explains why you have a total of 2 brain cells.

Branching out?

At least my 2 beats your none.

 

[Me]

Ken - Thanks for the kind words of encouragement. My
comments are directed to one person and only one person.
If that one person thinks they have a monopoly on being
rude and condescending I'm here@home to remind them that
what goes around comes around.

-----Original Message-----
You do know that 'Talking to yourself' is the first sign

of madness don't you? And you also

really shouldn't be so hard on yourself - Just keeping

taking the tablets and you'll be OK.

 

[Me]

At least my 2 beats your none.

clearly an example where quality is superior to quantity

 

[Harlan Grove]

No, not really. My statements of fact have a singular
audience and are not intended for general consumption.

More like a nemesis.

No, more like a court fool following a lobotomy. But good of you to confirm
your singularly valueless contributions.

 

[HappyGilmore]

No, more like a court fool following a lobotomy. But good of you to confirm
your singularly valueless contributions.