Conditional Formatting based on formula

[adbarnes81]

I'm having an issue when trying to format Column X to where it looks like this... If $W2 is greater than 5.99 but less than 7.00 and $X2 is less than 3.00, complete formatting, if not then do not complete formatting.

I hope that makes sense...lol

Any words of wisdom are greatly appreciated.

Thanks

 

[jeffreybrown]

If you I read your requests correctly...

=AND($W2>5.99,$W2<7,$X2<3,COUNT(W2:X2)=2)

 

[adbarnes81]

If you I read your requests correctly...

=AND($W2>5.99,$W2<7,$X2<3,COUNT(W2:X2)=2)

I tried this but nothing happened to the cell i was trying to format. I have the formatting already set but i might not have explained myself very well.

In cell W2 i have a number "6.72" since it falls within the >5.99, <7.00 criteria, i now have to check cell X2 to see if both are within criteria. X2 = "1.86" which means it falls within the <3.00 criteria. Since both have been met, cell X2 is suppose to have the formatting change applied to it. Basically a highlighted cell. I hope this explains what im trying to do.

Thanks for the help

 

[jeffreybrown]

With this formula in conditional formatting and applied to cell X2 with your requirements of 6.72 in W2 and 1.86 in X2 the cell is highlighted.

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=17 width=64>6.72

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; FONT-FAMILY: Verdana; BACKGROUND: #4f81bd; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" id=td_post_2694307 class=xl65 width=64>1.86

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=17>6.72</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=17>8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>1.86</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=17> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>1.86</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=17>6.72</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD></TR></TBODY></TABLE>

What about this is not right? With the table above what should the results be?

 

[adbarnes81]

With this formula in conditional formatting and applied to cell X2 with your requirements of 6.72 in W2 and 1.86 in X2 the cell is highlighted.

<table style="width: 96pt; border-collapse: collapse;" width="128" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="width: 48pt;" width="64" span="2"></colgroup><tbody><tr style="height: 12.75pt;" height="17"><td style="border: 0.5pt solid windowtext; background-color: transparent; width: 48pt; height: 12.75pt;" class="xl65" width="64" height="17">6.72

</td><td style="border: 0.5pt solid windowtext; width: 48pt; font-family: Verdana; background: none repeat scroll 0% 0% rgb(79, 129, 189); color: black; font-size: 10pt; font-weight: 400; text-decoration: none;" id="td_post_2694307" class="xl65" width="64">1.86

</td></tr><tr style="height: 12.75pt;" height="17"><td style="border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background-color: transparent; height: 12.75pt;" class="xl65" height="17">6.72</td><td style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;" class="xl65">4</td></tr><tr style="height: 12.75pt;" height="17"><td style="border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background-color: transparent; height: 12.75pt;" class="xl65" height="17">8</td><td style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;" class="xl65">1.86</td></tr><tr style="height: 12.75pt;" height="17"><td style="border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background-color: transparent; height: 12.75pt;" class="xl65" height="17">
</td><td style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;" class="xl65">1.86</td></tr><tr style="height: 12.75pt;" height="17"><td style="border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background-color: transparent; height: 12.75pt;" class="xl65" height="17">6.72</td><td style="border-width: medium 0.5pt 0.5pt medium; border-style: none solid solid none; border-color: windowtext; background-color: transparent;" class="xl65">
</td></tr></tbody></table>

What about this is not right? With the table above what should the results be?

It looks right, I applied the formula you posted in the conditional formatting but the cell X2 wont highlight. Is there something I'm missing? Does that formula go in conditional formatting or what?

 

[jeffreybrown]

Yes in conditional formatting.

Alt + OD >> Opens conditional formatting rules manager

New Rule >> Use a formula to determine which cells to format

Under Rule description enter the formula >> select format >> Ok

This is 2007 I don't have 2003

If you need some extra info: http://www.contextures.com/xlcondFormat01.html

 

[adbarnes81]

Excellent thank you. Now how would i be able to apply this formatting to $X$2:$X$101 to where they are all related to their corresponding cells. $W$2:$W$101 respectively?

 

[jeffreybrown]

I'm sorry read to quick. The formula I gave you would be applied to $W$2:$W$101. What do you want for column X? Basically the reverse?

In other words, if you apply this formula to both ranges, then in example one both W2 and X2 would be highlighted. Is this what you want?

 

[adbarnes81]

No, This formula works just fine. Only W2 is suppose to show formatting, and thats only if X2 and W2 meet their respective criteria. Once I set the range as $W$2:$W$101, will it continue to refer to X2 in the formula? Im looking to have an identical formula for X3-W3, X4-W4 and so on and so forth. Thats my next concern about formatting the W2-W101 range

 

[jeffreybrown]

The formula is set to apply to the whole range.

Just highlight the entire range stating with W2.

If you look at the formula and the use of the absoutes they will tell you what's happening.

=AND($W2>5.99,$W2<7,$X2<3,COUNT($W2:$X2)=2)

The dollar sign in front of the W and/or X locks the column and the row is absolute which means as you apply the formula down column W the column reference stays put but the row number can change.

This part COUNT($W2:$X2)=2 just makes sure both cells are filled out before the format will fire

Switch between relative, absolute, and mixed references