Conditional Formatting Formula

Tennisguuy

Well-known Member
Joined
Oct 17, 2007
Messages
564
Office Version
  1. 2016
Platform
  1. Windows
I wanted to use conditional formatting to turn a cell red if a certain condition was true. The formula I wanted to use was a double lookup so I test the formula in an actual cell to see if it was working the way I wanted. Below is the formula

IF(OR(B57="OR",B57="TX"),VLOOKUP(VLOOKUP(B57,SOV!BN6:BO205,2,FALSE),SOV!BO6:BQ205,3,FALSE)) the formula worked and return a value of 35 which is correct.

The problem is when I tried to modify the formula to be based on a condition of the result I am getting a formula error. I tried different variations but I wanted formula to be true if the result was greater than 40. I tried the following in a cell to see if it would work

IF(OR(B57="OR",B57="TX"),VLOOKUP(VLOOKUP(B57,SOV!BN6:BO205,2,FALSE),SOV!BO6:BQ205,3,FALSE))>0.4. I know this is not correct because it says TRUE in the cell but it should be false because the value is 35.

I also tried this IF(AND(OR(B57="OR",B57="tx"),(VLOOKUP(VLOOKUP(B57,SOV!BN6:BO205,2,FALSE),SOV!BO6:BQ205,3,FALSE)))>0.4 but that didn't work either
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Re: Conditional Formatting Formula Help

If this
IF(OR(B57="OR",B57="TX"),VLOOKUP(VLOOKUP(B57,SOV!BN6:BO205,2,FALSE),SOV!BO6:BQ205,3,FALSE))
Returns 35 you get
35>0.4
which is true
 
Upvote 0
Re: Conditional Formatting Formula Help

Thanks your correct that's what happens when you look at something too long a simple slip of the digit of .40 vs 40 can make all of the difference.

As I mentioned I tried testing the formula in a cell. That formula work when I changed it to 40. However when I put this formula into condition formatting I am getting an error that says There's a problem with this formula

=OR(B57="OR",B57="tx"),VLOOKUP(VLOOKUP(B57,SOV!BN6:BO205,2,FALSE),SOV!BO6:BQ205,3,FALSE)>40
 
Upvote 0
Re: Conditional Formatting Formula Help

You need to keep it in an IF like you posted originally
 
Upvote 0
Re: Conditional Formatting Formula Help

Thanks very much that work but I didn't think you could use an IF statement in conditional formatting.
 
Upvote 0
Re: Conditional Formatting Formula Help

You can as long as it's returning either true or false, which your's does as you then compare the result of the IF with the >40
 
Upvote 0
Re: Conditional Formatting Formula Help

ok thanks one last question. I have this formula in a template so it's blank until data is entered. When I put the formula into the conditional formatting I highlighted from B57:B157 if the statement is true it would make the cell fill red but when the template is blank and there are no states yet entered the entire column from B57 to B157 fills red bt when I enter a state then the red goes away until the condition is true. Is there a way to make the fill not red when no states are entered.
 
Upvote 0
I forgot to mention one other thing even when I enter a state if its not a state that is in the formula it stays red.

This is the formula I have I have more states listed from what I originally posted because I didn't want to put them all in until I got it to work

=IF(OR($B$57="AZ",$B$57="CA",$B$57="CO",$B$57="FL",$B$57="ID",$B$57="MT",$B$57="NM",$B$57="NV",$B$57="OK",$B$57="OR",$B$57="TX",$B$57="UT",$B$57="WA",$B$57="WY"),VLOOKUP(VLOOKUP($B57,SOV!$BN$6:$BO$205,2,FALSE),SOV!$BO$6:$BQ$205,3,FALSE))>40
 
Upvote 0
You can wrap that formula in an And like
Code:
=AND($B57<>"",IF(OR($B$57="AZ",$B$57="CA",$B$57="CO",$B$57="FL",$B$57="ID",$B$57="MT",$B$57="NM",$B$57="NV",$B$57="OK",$B$57="OR",$B$57="TX",$B$57="UT",$B$57="WA",$B$57="WY"),VLOOKUP(VLOOKUP($B57,SOV!$BN$6:$BO$205,2,FALSE),SOV!$BO$6:$BQ$205,3,FALSE))>40)
which will deal with blanks.
However in your OR you have locked $B$57, but in the lookup you have left the row free to move $B57. Is it meant to be like that.
 
Upvote 0
Thanks I had tried the same last night with the AND(B57<>"" in the formula but I had the last parenthesis in the wrong place. I didn't initially have the B57 as all absolutes with the OR statements but when I didn't the fill wasn't working the way I wanted so I tried making them all absolute. Everything is working now. I removed the absolutes. Thanks for all of your help. I really really appreciate it.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top