Trouble with conditional formatting with formulas

[willow1985]

I always seem to have trouble with conditional formatting.
Here are the formatting rules I have created:

What I need is as follows:

=$F2:$F1000="Y" If column F has the text "Y" in it then the entire row (from A:N) should format the text to green
=AND($F2:$F1000="N",$F2:$F1000<>"") If column F has the text "N" AND the cell beside it in Column E is not blank it should format the text to blue

The only one that seems to work is the =$E2:$E1000="" then format the font Red.
The other 2 seem to only affect column E for some reason and only work some of the time. I just cant figure it out.

Any help would be appreciated, this is probably one of the functions of excel I dread working with....

Thank you

Carla

 

[Fluff]

Data validation shouldn't make any difference.
In you recent image it does not look as though the data is in a Table, but in your images of the Rules manager it clearly states "This Table"

Is the data in a structured table & if so what is the range of it?

 

[Fluff]

In your dummy file the CF is working exactly how I would expect it to work.
Namely, if Column F has Y then whole row turns green & if it's N then the row turns blue.

 

[willow1985]

Sorry, the dummy file is not a table but the real file is. Either way I find both are having this issue. On the dummy file did you select N or Y from the drop down? as soon as I do it only formats the 3 columns for me

 

[willow1985]

I made the dummy file a table. Toggle between Y and N on the 1st or 2nd row for column F to see what I am referring to:

1575479166294_Conditional formatting.xlsx

 

[Fluff]

It works fine for me, I've changed the format to fill instead of font so it shows up here

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Scheduled Month:	Element:	Severity Level:	Employee:	Date Completed:	Filed Y/N	Previous Date	Finding Raised Y/N:	CAPA #:	Flag Date	Due Date	Immediate Attention Required Y/N	Code	Year
2	01/11/2019	Name 1	1	Employee 1	29/11/2019	N	30/01/2019	N	N/A	30/12/2019	30/01/2020	N	1	2019
3	01/11/2019	Name 2	1	Employee 2	29/11/2019	Y	28/02/2019	N	N/A	28/01/2020	28/02/2020	N	2	2020
4	01/12/2019	Name 3	1	Employee 3		Y	28/01/2019			28/12/2019	28/01/2020	N	3	2019
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:N1000	Expression	=$E2=""	text	NO
A2:N1000	Expression	=$F2="Y"	text	NO
A2:N1000	Expression	=$F2="N"	text	NO

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Scheduled Month:	Element:	Severity Level:	Employee:	Date Completed:	Filed Y/N	Previous Date	Finding Raised Y/N:	CAPA #:	Flag Date	Due Date	Immediate Attention Required Y/N	Code	Year
2	01/11/2019	Name 1	1	Employee 1	29/11/2019	Y	30/01/2019	N	N/A	30/12/2019	30/01/2020	N	1	2019
3	01/11/2019	Name 2	1	Employee 2	29/11/2019	N	28/02/2019	N	N/A	28/01/2020	28/02/2020	N	2	2020
4	01/12/2019	Name 3	1	Employee 3		Y	28/01/2019			28/12/2019	28/01/2020	N	3	2019
Sheet1

 

[willow1985]

This is what I am getting with the file:

As you can see only column E, F and G are affected and the other cells do not change.

 

[willow1985]

I changed the color so you can see it better. This is the exact file I uploaded so not sure what is going on....

 

[willow1985]

figured it out. Apparently formula automatic calculation was set to manual. No idea it had been set to this. As soon as I changed it to automatic it changed the entire row and not just the 3 cells.
Feeling sheepish. Strange that it would do 3 cells however....you would think with automatic calculation on manual that nothing would happen at all...
Anyway it is solved.

Thank you very much Fluff and sorry for taking up so much of your time

 

[willow1985]

I am going to write a VBA code that turns on automatic calculation as soon as the book is opened to avoid this in the future.

Take care!

 

[Fluff]

Glad you figured it out & thanks for the feedback.
It's odd that it still works for three columns, although if you scroll down so the row is hidden & then scroll back up. The entire row is colured correctly, so it looks to be more of a display thing.