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]

The formulae should be like
=$F2="Y"
=AND($F2="N",$F2<>"")
=$E2=""

 

[willow1985]

I see.... so ranges are not required then.

When I change the cell value in F to "Y" or "N" it still only formats column E for some reason however

 

[willow1985]

I entered the new formula's but it still only applies to column E and F when I toggle the text in Column F between "Y" and "N"

 

[Fluff]

Did you change the applies to range?
To highlight the row it should still be $A$2:$N$1000

 

[Fluff]

Looking at it again, the middle formula can just be
=$F2="N"
No need for the And.

What order do you want the rules to run in?
ie should col F having Y or N overrule the condition for col E is blank?

 

[willow1985]

Ok I changed the formula to =$F2="N"

If col E is blank it should overrule the other 2. I did put that formula first.
The applies to range is still: $A$2:$N$1000
which is why it is puzzling me that it is only formatting column E and F. No idea why this is....

 

[Fluff]

Does your table cover the range A2:N1000 with the header in row 1?

 

[willow1985]

I decided to test something and it appears it has a problem with column F having data validation. Column F is a drop down where you can only select Y or N

As soon as I remove the data validation it works.... Can this be fixed?

 

[willow1985]

I uploaded a dummy file if you need to work with something...

Conditional formatting.xlsx

When I checked off the box that states "apply these changes to all other cells with the same settings" in data validation it started to format column E, F and G but that was it.
I am really puzzled on this one...