stupid 3 limit on conditional formatting

  • Thread starter Thread starter ken
  • Start date Start date
K

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????
 
ken said:
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????
Click to expand...

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.
 
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
 
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
 
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
 
Harlan answered the question you asked, I'll try to answer the one
you perhaps should have asked.
Click to expand...


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
 
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.
 
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
 
Ken,

You can use conditional formatting with the formula

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

HTH
Anders Silvén
 
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.
 
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
Click to expand...
of madness don't you? And you also
really shouldn't be so hard on yourself - Just keeping
Click to expand...
taking the tablets and you'll be OK.
 
Me said:
No, not really. My statements of fact have a singular
audience and are not intended for general consumption.

More like a nemesis.
Click to expand...

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