Forum Discussion
Conditional Formatting in a Macro with AND statement
I have been struggling to get Conditional Formatting to work using the following type of formula:
=AND($E2="Tech Spec: Svc: Evaluate",$F2>10,$F2<=20)
I would like to format the cell in column F a certain color when the cell in column E equals a specific string and the cell value in column F is between 2 values. I know the AND statement is working when I test it in a cell with a formula like
=IF(AND($E2="Tech Spec: Svc: Evaluate",$F2>10,$F2<=20),"Yes","No")
But when I try to use the AND formula in Conditional Formatting it doesn't work with 3 conditions.
I am trying to build a script that will create a spreadsheet and apply the Conditional Formatting with an AND statement with 3 conditions, but I can't seem to get it to work even in a plain old spreadsheet.
Any tips or suggestions would be appreciated.
Your rule for conditional formatting works perfectly in my sheet.
5 Replies
- Thanks for all the responses. I think I was circling the solution and I wasn't googling the right question.
I finally got it to work with formula looking like this:
=IF(AND($E1="Tech Spec: Svc: Evaluate",$F1>10,$F1<=20),"True","False")
Turns out the IF and the True and False were needed for the formula to work.- That IF should NOT be needed. I'm really curious why it didn't work without it. Another thing you can try is using * instead of AND like this:
=($E1="Tech Spec: Svc: Evaluate")*($F1>10)*($F1<=20)
- =AND($E2="Tech Spec: Svc: Evaluate",$F2>=10,$F2<=20)
Add the "=" sign. Instead of "$F2>10" add "$F2>=10", so the result will be different for value "10". - the AND statement should work just fine. Just know/remember that the custom formula in the conditional formatting rule must be correct when applied to the upper left cell of the Applied To range set for that conditional formatting rule. Then using the $ on the aspects that should be locked (as it appears you have already done). So basically I would make sure the Applied To range is F2:Fsomething
Your rule for conditional formatting works perfectly in my sheet.
