Conditional Formatting Rule Not Working Properly

LtCmdrData

New Member
Joined
Jan 24, 2018
Messages
34
I'm trying to conditionally format a column based on three criteria. I'm using the AND function to evaluate three different cells in the same row and if all three are "true" then I want to conditionally format the first of those 3 cells. When I run the AND function down the side in a helper column to evaluate I get the answer I am looking for. When I try to apply the exact same formula in the Conditional Formatting Edit the Rule Description I get different results. Some of the results are correct but also it is highlighting cells it shouldn't. Can anyone shed any light on what is going wrong? I'm using Excel 2016. Thanks.
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,303
Office Version
  1. 365
Platform
  1. Windows
What is the formula & what is the applies to range?
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
Select the cells you want to highlight

Conditional Formatting
New Rule
Use a formula to determine...

=AND(A1=1,B1=2,C1=3)

Format as required
 

LtCmdrData

New Member
Joined
Jan 24, 2018
Messages
34
=AND(H3="LTL",OR(X3=1,X3=2),W3/T3>0.5)

So if all three conditions are met I want it to fill in cell H3 and so on down column H. It is highlight some that it should but not all. It is even highlighting the column heading H1!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,303
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

What is the Applies to range?
Is it the whole of col H?
 

LtCmdrData

New Member
Joined
Jan 24, 2018
Messages
34
Yes except of course the column heading. The data will probably only run down to about row 200 but the report could vary each time.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,303
Office Version
  1. 365
Platform
  1. Windows
The applies to range needs to start in H3, other wise you will get the problems that you are experiencing.
If you are unlikely to go past row 200 set the range to $H$3:$H$200
 

Watch MrExcel Video

Forum statistics

Threads
1,108,984
Messages
5,526,060
Members
409,682
Latest member
HisHailo

This Week's Hot Topics

Top