Change color in cell based on range of values, and change the color of same cell based on value in another range once that's entered.

[nscaria00]

Hi, I need help as I am totally new to VBA.
I have an excel document that's like a dashboard. I have Column 'N' for EO status'. User enters information in rows from A6 to E6 (same way for each rows). When someone enters infomartion, then Column 'N against that row needs to turn RED. Once Management signs off, which is in column 'F to M', then Column 'N' against that row needs to turn to Green.
Can you please help developing a code.

 

[awoohaw]

something like this:?

mr excel questions 25.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2
3
4
5	Data1	Data2	Data3	Data4	Data5	NewData1	NewData2	NewData3	NewData4	NewData5	NewData6	NewData7
6	16	13	19	20	19	16	19	17	18	16	15	16	14		TRUE	TRUE
7	13	11	12	19	17										FALSE	TRUE
8															FALSE	FALSE
9						19	16	17	15	14	18	11	6		FALSE	FALSE
Sheet7

Cell Formulas
Range	Formula
O6:O9	O6	=COUNTA($A6:$M6)=COLUMNS($A6:$M6)
P6:P9	P6	=COUNTA($A6:$E6)=COLUMNS($A6:$E6)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
N6:N9	Expression	=COUNTA($A6:$M6)=COLUMNS($A6:$M6)	text	NO
N6:N9	Expression	=COUNTA($A6:$E6)=COLUMNS($A6:$E6)	text	NO

 

[nscaria00]

Thank you for taking time to help!. I tried cells with Conditional formatting as you mentioned above.
It takes only one condition at a time and whatever condition stays on top, it will pull that one.
Sorry, I am just trying to learn excel and formulas. May be I selected wrong. I attach both screen shots.
a) I included both formulas , and b) the conditional formatting formula where I added the above formula.

 

[awoohaw]

in the conditional formatting rules you have options to go all the way through the rules for that cell or to stop when a condition is met.

additionally in columns O and P I have written the Formulas that are copied into the conditional formatting rules, and used to validate that I have it correct.

 

[nscaria00]

I tried alot and your solution above is not working. I entered the formula you have given and arranged conditional formatting rules in sequence. But not working. See attached below. It's not turning to green even after entering all values.

 

[awoohaw]

Did you build a formula that resolved to TRUE? I can assure you that if you do that then you will have the formula to put into the conditional formatting formula bar.

 

[nscaria00]

Nope, Not working. Attaching the actual form. Can you take a look and correct if my formula is wrong.
Row 6_-> Evenif I have one name not entered--> showing as Green (should show as Red)
Row 7--> Only entered A7-E7 --> SHould show N7 as Red (SHowing as green).
Not sure why!.

 

[awoohaw]

Put your file in a dropbox, please.

 

[nscaria00]

Trial form -Rev 2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
2	Year 2023
3	EO #	Initiator	Date	Part #	Change Description	Completion Status							Completion date	EO Status
4						Accounting	Purchasing	Customer Service	Quality	Engineering	Production	Final Sign off
5						Pat	Mat	Leah	John	David	Anish	Anil	03/27/2023
6	23-02	adam	2023-04-28	6655	545	Pat	Mat	Leah		David	Anish	Anil	2023-04-28	EO 23-02
7	23-03	adam	2023-04-28	152	3541									EO 23-03
Engineeeing Order master file

Cell Formulas
Range	Formula
F6	F6	='23-02'!$D$44
G6	G6	='23-02'!$D$45
H6	H6	='23-02'!$D$46
I6	I6	='23-02'!$D$47
J6	J6	='23-02'!$D$48
K6	K6	='23-02'!$D$49
L6	L6	='23-02'!$F$52
M6	M6	='23-02'!$D$52
F7	F7	='23-03'!$D44
G7	G7	='23-03'!$D45
H7	H7	='23-03'!$D46
I7	I7	='23-03'!$D47
J7	J7	='23-03'!$D48
K7	K7	='23-03'!$D49
L7	L7	='23-03'!$F52
M7	M7	='23-03'!$D52

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L6	Cell Value	contains ""	text	NO
L6	Cell Value	contains ""	text	NO
L6	Cell Value	contains ""	text	NO
L6	Cell Value	contains ""	text	NO
L6	Cell Value	contains ""	text	NO
G6:G7	Cell Value	=$G$5	text	NO
N6	Expression	=COUNTA($A6:$M6)=COLUMNS($A6:$M6)	text	NO
N6	Expression	=COUNTA($A6:$E6)=COLUMNS($A6:$E6)	text	NO
N7	Expression	=COUNTA($A7:$M7)=COLUMNS($A7:$M7)	text	NO
N7	Expression	=COUNTA($A7:$E7)=COLUMNS($A7:$E7)	text	NO

 

[awoohaw]

I thought you were going to upload a file? The xl2bb is great, but you reference other worksheets, it is causing extra work.