Conditional Formatting "Less Than" formula on multiple cells ignoring "0" value

[Brendon Donald]

Hi,

I am looking for a conditional formatting formula which compares one cell value (CB51) against 6 other cells values (CB30, CB31, CB41, CB43, CB66 and CB69) to determine whether it is less than any of the 6 cells values and turns green but also ignores if any of the 6 cells values that are 0?

This is what I have and it doesn't work????
=AND(ISBLANK($CB$41)=FALSE,ISBLANK($CB$43)=FALSE,ISBLANK($CB$66)=FALSE,ISBLANK($CB$69)=FALSE,$CB$51<$CB$30,$CB$51<$CB$31,$CB$51<$CB$41,$CB$51<$CB$43,$CB$51<$CB$66,$CB$51<$CB$69)

Thanks,

 

[Fluff]

In what way doesn't it work?

Can you post some sample data, along with expected result.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Brendon Donald]

In what way doesn't it work?

Can you post some sample data, along with expected result.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Hi Fluff, I followed the instructions, so hopefully you can paste the below into your spreadsheet, I also added some note to my spreadsheet before copying trying to explain what I want my spreadsheet to do.

Book1
	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB
30		These cells to change to green if CB30 is between CB51 & CB53, if not then turn red		6		m	100		yds																													6	0.01	6.0100
31		These cells to change to green if CB30 is between CB51 & CB53, if not then turn red		9		m	1500		yds																													9	0.15	9.1500
32
33
34
35
36
37
38
39
40
41		These cells to change to green if CB41 is between CB51 & CB53, if lower or higher than CB51 or CB53 turn red, or stay blank if no value is entered		5		m			yds																													5	0	5.0000
42
43		These cells to change to green if CB43 is between CB51 & CB53, if lower or higher than CB51 or CB53 turn red, or stay blank if no value is entered		5		m			yds																													5	0	5.0000
44
45
46
47
48
49
50
51		These cells to change green if CB51 has the lowest value or turn red if its greater than any of the values in CB30, CB31, CB41(if contains a value), CB43(if contains a value), CB66(if contains a value), CB69(if contains a value),		1		m			yds																													1	0	1.0000
52
53		These cells to change green if CB53 has the greatest value or turn red if its lower than any of the values in CB30, CB31, CB41(if contains a value), CB43(if contains a value), CB66(if contains a value), CB69(if contains a value),		10		m			yds																													10	0	10.0000
54
55
56
57
58
59
60
61
62
63
64
65
66		These cells to change to green if CB66 is between CB51 & CB53, if lower or higher than CB51 or CB53 turn red, or stay blank if no value is entered				m			yds																													0	0	0.0000
67
68
69		These cells to change to green if CB69 is between CB51 & CB53, if lower or higher than CB51 or CB53 turn red, or stay blank if no value is entered		4		m			yds																													4	0	4.0000
Sheet1

Cell Formulas
Range	Formula
BZ69,BZ66,BZ53,BZ51,BZ43,BZ41,BZ30:BZ31	BZ30	=AR30
CA69,CA66,CA53,CA51,CA43,CA41,CA30:CA31	CA30	=AU30/10000
CB69,CB66,CB53,CB51,CB43,CB41,CB30:CB31	CB30	=BZ30+CA30

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AR53,AU53	Expression	=AND($CB$53>$CB$30,$CB$53>$CB$31,$CB$53>$CB$41,$CB$53>$CB$43,$CB$53>$CB$66,$CB$53>$CB$69)	text	NO
AR51,AU51	Expression	=AND($CB$41<>0,$CB$43<>0,$CB$66<>0,$CB$69<>0,$CB$51<$CB$30,$CB$51<$CB$31,$CB$51<$CB$41,$CB$51<$CB$43,$CB$51<$CB$66,$CB$51<$CB$69)	text	NO
AR31,AU31	Expression	=AND($CB$31>$CB$51,$CB$31<$CB$53)	text	NO
AU69	Expression	=AND($CA$69>$CA$51,$CA$69<$CA$53)	text	NO
AR69,AU69	Expression	=AND($CB$69>$CB$51,$CB$69<$CB$53)	text	NO
AU66	Expression	=AND($CA$66>$CA$51,$CA$66<$CA$53)	text	NO
AR66,AU66	Expression	=AND($CB$66>$CB$51,$CB$66<$CB$53)	text	NO
AR30,AU30	Expression	=AND($CB$30>$CB$51,$CB$30<$CB$53)	text	NO
AR53,AU53	Expression	=AND(ISBLANK($CB$69)=FALSE, $CB$69>$CB$53, $CB$53>0.0001)	text	NO
AR53,AU53	Expression	=AND(ISBLANK($CB$66)=FALSE, $CB$66>$CB$53, $CA$53>0.0001)	text	NO
AR53,AU53	Expression	=AND($CB$43>$CB$53, $CB$53>0.0001)	text	NO
AR53,AU53	Expression	=AND($CB$41>$CB$53, $CB$53>0.0001)	text	NO
AR53,AU53	Expression	=AND($CB$31>$CB$53, $CB$53>0.0001)	text	NO
AR53,AU53	Expression	=AND($CB$30>$CB$53, $CB$53>0.0001)	text	NO
AR51,AU51	Expression	=AND(ISBLANK($CB$69)=FALSE, $CB$69<$CB$51,$CB$69>0)	text	NO
AR51,AU51	Expression	=AND(ISBLANK($CB$66)=FALSE, $CB$66<$CB$51,$CB$66>0)	text	NO
AR51,AU51	Expression	=AND(ISBLANK($CB$43)=FALSE, $CB$43<$CB$51,$CB$43>0)	text	NO
AR51,AU51	Expression	=AND(ISBLANK($CB$41)=FALSE, $CB$41<$CB$51,$CB$41>0)	text	NO
AR51,AU51	Expression	=AND($CB$31<$CB$51)	text	NO
AR51,AU51	Expression	=AND($CB$30<$CB$51)	text	NO
AR43,AU43	Expression	=AND($CB$43>$CB$51,$CB$43<$CB$53)	text	NO
AR41,AU41	Expression	=AND($CB$41>$CB$51,$CB$41<$CB$53)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
BA30:BB30	List	='[PK - TRACK ACCESS-SWP REQUEST FORM.xlsx.xlsm]A1 Do Not Use'!#REF!
BC32:BW32	List	='[PK - TRACK ACCESS-SWP REQUEST FORM.xlsx.xlsm]A1 Do Not Use'!#REF!
BC33:BW34	List	='[PK - TRACK ACCESS-SWP REQUEST FORM.xlsx.xlsm]A1 Do Not Use'!#REF!
AO40:AP40	List	='[PK - TRACK ACCESS-SWP REQUEST FORM.xlsx.xlsm]A1 Do Not Use'!#REF!
AU40:AW40	List	='[PK - TRACK ACCESS-SWP REQUEST FORM.xlsx.xlsm]A1 Do Not Use'!#REF!
BC69	List	='C:\Users\BDonald\Desktop\AMOC WPP\[Site Inspection Report - BD Copy rev4.xlsm]Do not use'!#REF!
AY51:BB67	List	='C:\My Documents\AMOC WPP\[Track access. SWP request form.xlsm]A2 Team Info'!#REF!
BC51:BW67	List	='[PK - TRACK ACCESS-SWP REQUEST FORM.xlsx.xlsm]A1 Do Not Use'!#REF!
BC38:BW38	List	='[PK - TRACK ACCESS-SWP REQUEST FORM.xlsx.xlsm]A1 Do Not Use'!#REF!
BC39:BW43	List	='C:\My Documents\AMOC WPP\[Track access. SWP request form.xlsm]A2 Team Info'!#REF!
BC44:BW47	List	='[PK - TRACK ACCESS-SWP REQUEST FORM.xlsx.xlsm]A1 Do Not Use'!#REF!
AO39:AW39	List	=$K$2:$K$5

 

[Fluff]

Thanks for that.
For CB51 you can use

=MIN(CB51,CB30:CB31,IF(CB41=0,10^10,CB41),IF(CB43=0,10^10,CB43),IF(CB66=0,10^10,CB66),IF(CB69=0,10^10,CB69))=CB51

and for CB53

=MAX(CB53,CB30:CB31,CB41,CB43,CB66,CB69)=CB53

 

[Brendon Donald]

Thanks for that.
For CB51 you can use

=MIN(CB51,CB30:CB31,IF(CB41=0,10^10,CB41),IF(CB43=0,10^10,CB43),IF(CB66=0,10^10,CB66),IF(CB69=0,10^10,CB69))=CB51

and for CB53

=MAX(CB53,CB30:CB31,CB41,CB43,CB66,CB69)=CB53

Thank you very much Fluff, that's it working now.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Brendon Donald]

HI Fluff,

Are you also good with VBA?

Looking for a code that will changes a number of individual cell based on a dropdown list selection.

Cell A1 dropdown selection is either "miles/chains" or "miles/yard" and I need cells C3, C9, C16 & C30 to change to "ch" if "miles/chains" is selected or to "yds" if "miles/yards" is selected?

Thanks in advance.

 

[Fluff]

As that is a totally different question, it needs a new thread. Thanks