Conditional formatting - multiple criteria
 

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

Conditional formatting - multiple criteria
 

Sorry - found an error in the original post.

Should be: If F4 is not , color the cell (or cells if multiple exist with
the same
value) that have the lowest price in the range J4:Q4.


"jmcclain" wrote:

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

Conditional formatting - multiple criteria
 

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

Conditional formatting - multiple criteria
 

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

.

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

.