Conditional Formatting

[carrieebacon]

I suck at this (I am sure it is simple and I am just clueless) but I need a rule for column D that says "if the value in cell =TOLERANCE!D6 is less than 0.10 when subtracted from =DESIGN!D6, format the cell green. If the value is greater than 0.10 but less than 0.50, format the cell yellow. If the value is greater than 0.50, format the cell red." I need this to apply down the whole column for each respective cell. Should I even be using conditional formatting for this?

 

[etaf]

Should I even be using conditional formatting for this?

yes

3 rules , 1 for each colour

column D in what sheet ?

=(DESIGN!D6-TOLERANCE!D6) < 0.1

format green
for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
D6:D1000 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=(DESIGN!D6-TOLERANCE!D6) < 0.1

Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

=(DESIGN!D6-TOLERANCE!D6) > 0.5
RED

then
=(DESIGN!D6-TOLERANCE!D6) < = 0.5

put in that order and use stop if true

so that when less than .1 it stops and does not go onto <=0.5

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[carrieebacon]

This worked! Is there any way I can set it to if a value is not in a cell yet, it will not be formatted?

 

[etaf]

=AND( DESIGN!D6 <> "", TOLERANCE!D6 <>"", ( DESIGN!D6-TOLERANCE!D6 )< 0.1)
=AND( DESIGN!D6 <> "", TOLERANCE!D6 <>"", ( DESIGN!D6-TOLERANCE!D6 )> 0.5)
=AND( DESIGN!D6 <> "", TOLERANCE!D6 <>"", ( DESIGN!D6-TOLERANCE!D6 )< = 0.5)
that will do if both cells have to be blank to colour

is that what you meant ??

other wise to test if either
add an OR

=AND( OR(DESIGN!D6 <> "", TOLERANCE!D6 <>""), ( DESIGN!D6-TOLERANCE!D6 )< 0.1)
=AND( OR(DESIGN!D6 <> "", TOLERANCE!D6 <>""), ( DESIGN!D6-TOLERANCE!D6 )> 0.5)
=AND( OR(DESIGN!D6 <> "", TOLERANCE!D6 <>""), ( DESIGN!D6-TOLERANCE!D6 )< = 0.5)

 

[carrieebacon]

ASB COPY

This is a link to a mock version of the spreadsheet. I'm probably not explaining it very well, but I would like to input data into columns D, F, I, and L and once a value is inputted, the cell will format.

 

[carrieebacon]

and as for the formula, let me reword this. If the difference between the values in cell DESIGN!D6 and TOLERANCE!D6 is greater than 0.5, highlight the cell red etc. etc.

 

[etaf]

thanks for the link , that will make things easier

however

but I need a rule for column D that says "if the value in cell =TOLERANCE!D6 is less than 0.10 when subtracted from =DESIGN!D6, format the cell green. If the value is greater than 0.10 but less than 0.50, format the cell yellow. If the value is greater than 0.50, format the cell red." I need this to apply down the whole column for each respective cell. Should I even be using conditional formatting for this?

but I need a rule for column D

On what Sheet ?

=TOLERANCE!D6 is less than 0.10 when subtracted from =DESIGN!D6, format the cell green.

Why ROW 6 ?

and not row 2 - where the data seems to start on both sheets

but I would like to input data into columns D, F, I, and L and once a value is inputted, the cell will format.

On what sheets

it can be done with conditional formatting and some logic tests to check if the cells are blank or have data entered

If the difference between the values in cell DESIGN!D6 and TOLERANCE!D6 is greater than 0.5, highlight the cell red etc. etc.

Yep, that was one of the rules to colour RED

=(DESIGN!D6-TOLERANCE!D6) > 0.5
RED

HIGHLIGHT WHAT CELL?


DESIGN SHEET

ASB COPY.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	STR. ID	ID	TOP	ELV.	INV.	ELV.	DIR.	INV.	ELV.	DIR.	INV.	ELV.	DIR.
2	CI	11	FR:	19.39	INV. IN	6.08	W	INV. IN	9.08	N	INV. OUT	6.08	E
3	CI	15	FR:	19	INV. IN	3	N	INV. IN	9	E	INV. OUT	3	S
4	CI	154	FR:	18.15	INV. IN	12.16	E	INV. OUT	12.16	W
5	CI	155	FR:	18.01	INV. IN	12.4	E	INV. OUT	12.4	W
6	CI	156	FR:	18.63	INV. IN	12.64	E	INV. OUT	12.64	W
7	CI	157	FR:	18.01	INV. IN	12.81	E	INV. OUT	12.81	W
8	CI	164	FR:	18.56	INV. OUT	13	W
9	CI	165	FR:	18.56	INV. IN	11.59	E	INV. OUT	11.59	W
10	CI	167	FR:	18.23	INV. OUT	13	E
11	CI	169	FR:	18.1	INV. IN	12.56	N	INV. OUT	12.56	S
12	CI	172	FR:	18.21	INV. OUT	13	S
13	CI	173	FR:	14.04	INV. OUT	3	E
14	CI	175	FR:	14.91	INV. OUT	3	NE
15	CI	185	FR:	18.06	INV. IN	8.26	W	INV. OUT	7.75	SE
16	CI	186	FR:	18.19	INV. OUT	12	E
DESIGN

TOLARANCE SHEET

ASB COPY.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	STR. ID	ID	TOP	ELV.	INV.	ELV.	DIR.	INV.	ELV.	DIR.	INV.	ELV.	DIR.
2	CI	11	FR:		INV. IN	-19.5	W	INV. IN	-19.3	N	INV. OUT	-18.2	E
3	CI	15	FR:		INV. IN	-18.5	N	INV. IN	-18.4	E	INV. OUT	-18.4	S
4	CI	154	FR:		INV. IN	-11.162	E	INV. OUT	-9.762	W
5	CI	155	FR:		INV. IN		E	INV. OUT		W
6	CI	156	FR:		INV. IN		E	INV. OUT		W
7	CI	157	FR:		INV. IN		E	INV. OUT		W
8	CI	164	FR:		INV. OUT		W
9	CI	165	FR:		INV. IN		E	INV. OUT		W
10	CI	167	FR:		INV. OUT		E
11	CI	169	FR:		INV. IN		N	INV. OUT		S
12	CI	172	FR:		INV. OUT		S
13	CI	173	FR:		INV. OUT		E
14	CI	175	FR:		INV. OUT		NE
15	CI	185	FR:		INV. IN		W	INV. OUT		SE
16	CI	186	FR:		INV. OUT		E
TOLERANCE

 

[carrieebacon]

Oop sorry, when I made the copy spreadsheet in excel I copied from row 5 down because the company logo is in row 1-4. So yes in the ASB COPY the data starts in row 2. The formula would be in the tolerance sheet. So I am going to use F6 for example. In design, the value is 6.08. In tolerance, it is -19.5, which means it's clearly incorrect by ALOT. So because the difference is greater than 0.50, the cell F6 in tolerance would be red. If a value in design said 5.90 but in tolerance it said 5.88, the cell would be green because the difference is less than 0.10. If a value in design said 5.90, but in tolerance it said 5.60, the cell would be yellow because the difference is greater than 0.10 but less than 0.50.

 

[etaf]

ok, so i can add rows to 1-4 so we are on the same row as needed

so we want cell tolerance!F to be coloured - NOT D
and only when a number is entered into tolerance!F

so we still use - ????
=DESIGN!F6-TOLERANCE!F6 (will negative numbers still work for you - ... with the tolerance - so is -0.6 wrong as that is NOT greater than 0.5 - BUT maybe we need to use ABS() regardeless than of sign -
6.08 - - 19.5
will give a plus
which is 25.58

ANYWAY

and then test for a value > 0.5
and only if
TOLERANCE!F6
has a value
=AND( TOLERANCE!F6<>"", (DESIGN!F6-TOLERANCE!F6)>0.5)

Select F6:F1000 or what ever range
and use the formula
=AND( TOLERANCE!F6<>"", (DESIGN!F6-TOLERANCE!F6)>0.5)

see below
I will add to dropbox for a day or 2
what about
but I would like to input data into columns D, F, I, and L

No data is in D in this example - so should that not colour

feels like we may need to go through a few iterations to get this right - happy to do that

so below is just RED

dropbox link

ASB COPY -ETAF1.xlsx

Shared with Dropbox

www.dropbox.com

only for a few days

hence the xl2bb below - which can stay and help others

ASB COPY.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2
3
4
5	STR. ID	ID	TOP	ELV.	INV.	ELV.	DIR.	INV.	ELV.	DIR.	INV.	ELV.	DIR.
6	CI	11	FR:		INV. IN	-19.5	W	INV. IN	-19.3	N	INV. OUT	-18.2	E		25.58
7	CI	15	FR:		INV. IN	-18.5	N	INV. IN	-18.4	E	INV. OUT	-18.4	S
8	CI	154	FR:		INV. IN	-11.162	E	INV. OUT	-9.762	W
9	CI	155	FR:		INV. IN	12.4	E	INV. OUT		W
10	CI	156	FR:		INV. IN		E	INV. OUT		W
11	CI	157	FR:		INV. IN		E	INV. OUT		W
TOLERANCE

Cell Formulas
Range	Formula
O6	O6	=DESIGN!F6-TOLERANCE!F6

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F6:F63	Expression	=AND( F6<>"", (DESIGN!F6-F6)>0.5)	text	NO

 

[etaf]

added the other 2 rules for green and amber

and used your values - for green / amber in rows 7 and 8

ASB COPY -ETAF2.xlsx

Shared with Dropbox

www.dropbox.com

ASB COPY -ETAF1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2
3
4
5	STR. ID	ID	TOP	ELV.	INV.	ELV.	DIR.	INV.	ELV.	DIR.	INV.	ELV.	DIR.
6	CI	11	FR:		INV. IN	-19.5	W	INV. IN	-19.3	N	INV. OUT	-18.2	E	25.58
7	CI	15	FR:		INV. IN	5.88	N	INV. IN	-18.4	E	INV. OUT	-18.4	S	0.02
8	CI	154	FR:		INV. IN	5.6	E	INV. OUT	-9.762	W				0.3
9	CI	155	FR:		INV. IN	12.4	E	INV. OUT		W				0
10	CI	156	FR:		INV. IN	13	E	INV. OUT		W				-0.36
11	CI	157	FR:		INV. IN	14	E	INV. OUT		W				-1.19
12	CI	164	FR:		INV. OUT	-12	W							25
13	CI	165	FR:		INV. IN	11.09	E	INV. OUT		W				0.5
14	CI	167	FR:		INV. OUT	13	E							0
15	CI	169	FR:		INV. IN	12.06	N	INV. OUT		S				0.5
16	CI	172	FR:		INV. OUT		S							13
17	CI	173	FR:		INV. OUT		E							3
18	CI	175	FR:		INV. OUT		NE							3
19	CI	185	FR:		INV. IN		W	INV. OUT		SE				8.26
TOLERANCE

Cell Formulas
Range	Formula
N6:N19	N6	=DESIGN!F6-TOLERANCE!F6

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F6:F63	Expression	=AND( F6<>"", (DESIGN!F6-F6)<0.1)	text	YES
F6:F63	Expression	=AND( F6<>"", (DESIGN!F6-F6)<=0.5)	text	YES
F6:F63	Expression	=AND( F6<>"", (DESIGN!F6-F6)>0.5)	text	YES