Posted to microsoft.public.excel.worksheet.functions
|
|
Conditional formatting - multiple criteria
Bernard,
I figured out the problem and it is working now. Thanks for the direction.
"jmcclain" wrote:
Hi Bernard,
Let me re-state my issue - the more I re-read my original post, the more I
think I could have stated it in a clearer fashion.
F4 is our retail
J4:Q4 is our competitors retails (some are blanks)
I need to highlight F4 if we are the lowest price OR highlight the lowest
price(s) in F4:Q4
Can you help?
Much appreciation.
Jon
"Bernard Liengme" wrote:
1) Trying to compare cells J4:Q4 to cell F4. F4 will be colored yellow if
F4all cells in the range of J4:Q4 excluding blanks.
Select F4 , open Format | Conditional Formatting and use Formulas Is:
=SUMPRODUCT(--(F4J4:Q4))=COUNT(F4:Q4)
2) If F4is not , color the cell (or cells if multiple exist with the same
value) that have the lowest price in the range J4:Q4. (I have assumed you
meant F4 not J4 in the first few words)
Select J4:Q4 and enter this for Formula Is
=AND(J4=MIN($J$4:$Q$4),SUMPRODUCT(--($F$4$J$4:$Q$4))<COUNT($F$4:$Q$4))
Debra Dalgleish explains Cond Formatting
http://www.contextures.com/xlCondFormat01.html
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
"jmcclain" wrote in message
...
Using Excel 2003...
Trying to compare cells J4:Q4 to cell F4. F4 will be colored yellow if
F4all cells in the range of J4:Q4 excluding blanks.
If J4 is not , color the cell (or cells if multiple exist with the same
value) that have the lowest price in the range J4:Q4.
Can anyone suggest the proper formula?
Regards,
Jon
.
|