Conditional Formatting Formula

[ManofKent]

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

 

[Fluff]

Hi & welcome to MrExcel
You haven't given us much to go on, but maybe
=AND($G2<>"",G2=MIN($G2:$M2))

 

[ClaireS]

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).

 

[ManofKent]

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

 

[Fluff]

In that case they are not blank, with my formula I get

Book1
	G	H	I	J	K	L	M
2	0.904076	4.099045	41961	0.026395	0.171442	0.008013	0.162466
3		4.073879	41954	0.135846	0.007482		0.368573
4	0.999065		41947		0.035516	0.229067	0.511311
5	0.994699	3.713642	41940	0.564244	0.869977	0.88855	0.751162
6	0.967199	3.774746	41933	0.344501	0.689601	0.954096	0.586942
7	0.965564	3.919768	41926	0.061651	0.059692	0.686299	0.248297
8		3.9569	41919	0.086698	0.022205	5.61E-07	0.294444
9	0.993389	4.104325		0.030457		0.048071	0.17452
10	0.555707	3.949629	41905	0.001678	0.644046	0.99226	0.040965
11	-0.09199	4.029621	41898	0.082306	0.369035		0.286891
12	0.835513	3.997745	41891	0.163243	0.16515	0.026545	0.404033
Paste

 

[ManofKent]

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]

 

[ClaireS]

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?

 

[ManofKent]

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.

 

[Fluff]

Do the cells contain a formula? if so what is it?