Conditional Formatting Formula

ManofKent

New Member
Joined
Aug 8, 2019
Messages
4
Dear Forum

I have a table of data; Columns G to M. This range contains prices, however some columns have no prices so are blank

I have added conditional formatting to highlight the lowest price using the Min function, however this highlights all of the blank cells

I have tried to correct this by adding another formula within the conditional formatting box that uses ISBlank and have ticked the Stop if True button. This almost works however if you enter a price in say G2 and it is the lowest and enter a lower price in say J2, the conditional formatting moves off G2 but does not move to J2.

This is driving me crazy, therefore I would appreciate anyone's help.

Regards

Dave
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
81,755
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel
You haven't given us much to go on, but maybe
=AND($G2<>"",G2=MIN($G2:$M2))
 
Last edited:
Upvote 0

ClaireS

Board Regular
Joined
Jul 29, 2013
Messages
155
Office Version
  1. 365
Platform
  1. Windows
Welcome to the forum!

Use the built-in "Bottom 10..." rule. This ignores blanks and text entries. Set it to Bottom 1 for the minimum value.
Or use this formula (assuming your range to format is G2:M10): =G2=MIN($G$2:$M$10).
 
Upvote 0

ManofKent

New Member
Joined
Aug 8, 2019
Messages
4
Thank you for your reply, sorry if my first post was a little unclear.

Your solution works, however it does not ignore cells in the range that are blank, these also get highlighted and I want to ignore blanks cells
 
Upvote 0

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
81,755
Office Version
  1. 365
Platform
  1. Windows
In that case they are not blank, with my formula I get


Book1
GHIJKLM
20.9040764.099045419610.0263950.1714420.0080130.162466
34.073879419540.1358460.0074820.368573
40.999065419470.0355160.2290670.511311
50.9946993.713642419400.5642440.8699770.888550.751162
60.9671993.774746419330.3445010.6896010.9540960.586942
70.9655643.919768419260.0616510.0596920.6862990.248297
83.9569419190.0866980.0222055.61E-070.294444
90.9933894.1043250.0304570.0480710.17452
100.5557073.949629419050.0016780.6440460.992260.040965
11-0.091994.029621418980.0823060.3690350.286891
120.8355133.997745418910.1632430.165150.0265450.404033
Paste
 
Upvote 0

ManofKent

New Member
Joined
Aug 8, 2019
Messages
4
Hi Claire

Thank you for your reply. This worked but like the other suggestion, only in part. It seems like the formula is only being applied to certain cells, as some numbers are being highligted and some are not.

The reference area I am using is [FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=$G$2:$M$134

Any help here much appreciated
[/FONT]
 
Upvote 0

ClaireS

Board Regular
Joined
Jul 29, 2013
Messages
155
Office Version
  1. 365
Platform
  1. Windows
Can you supply any further information? Are the cells being calculated by a formula (and if so, what)? What sort of range of values should result? Abd I didn't check whether you want the CF once for the whole block or individually per column?
 
Upvote 0

ManofKent

New Member
Joined
Aug 8, 2019
Messages
4
What I did not explain is that the formula needs to work across a row range and not a column range. Each row is a different product, so I want the lowest price in that field to be highlighted and where there is not a price, it ignores the blanks.
 
Upvote 0

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
81,755
Office Version
  1. 365
Platform
  1. Windows
Do the cells contain a formula? if so what is it?
 
Upvote 0

Forum statistics

Threads
1,187,061
Messages
5,961,359
Members
438,539
Latest member
muimonk

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top