Conditional Formatting – testing two cells

[jcountzler]

I have a budget analysis worksheet that compares budgeted amounts to actual results ($ sales, volume, etc.). A sample of the worksheet is attached. The worksheet calculates $ and % budget variances. I am attempting to construct a conditional formatting that would apply CF, in this case a pattern (cell shading), based upon the result of both the $ and % budget variance.

For example, if the $ budget variance is less than or greater than $5,000 AND the % budget variance is less than or greater than 5%, the cell(s) would have the CF applied. I have manually shaded the cells in the attached worksheet to show the desired CF result. For purposes of the production worksheet I would reference a range name that contains the desired x$ and x% values to test against.

I am coding this in Excel 2003 SP3.

Thank you in advance for any input and guidance on my query.

[masteff]

So, select your range E4:F24, on the menu select Format/Conditional Formatting, in the very first drop down change it from "Cell Value Is" to "Formula Is", and enter =AND(ABS($E4)>5000,ABS($F4)>0.05) , then click the "Format" button and set your pattern.

[jcountzler]

Thank you so much for this formula. It is quick and concise.