CF - using a formula to determine which cells to format...ISSUE

**AMSBenji** · 05-01-2013, 10:11 PM

I understand how to accomplish CF based on a value in another cell. However I'm having a problem when that cell is formula driven. File is saved as an XLSX, using 2007...ummm not sure what other variables there might be.

Simply put

=$AM23=5 should highlight cell R23 blue. It will do this if I copy and paste AM23 special so it is truly a number. However the cell is generating a 5 based on a VLOOKUP formula from the adjacent sheet.

Any ideas out there?

**AMSBenji** · 05-01-2013, 10:59 PM

The issues are on the "Main" sheet.

L-W have the CF rules defined based on the formula in AG:AR.

Cells AG23:AR33 have the first option to utilize as the formula. This option reports the correct number in columns AG:AR, but the CF won't work.

Cells AG35:AR49 have the second option...this option works 100% of the time if the qty in L:W is an exact match to the supporting sheet, however that only occurs about 50% of the time in my line of work.

Thanks

**benishiryo** · 05-01-2013, 11:30 PM

ok found the problem. in PO sheet column Z, you used the RIGHT formula. it makes the result a text. you could do the following options:
1) make Z2 in PO sheet:
=RIGHT(W2)+0

2) make AG23 in MAIN sheet:
=VLOOKUP($A23&L23,PO!$Y$2:$Z$5000,2,FALSE)+0

3) make the Conditional Formatting:
=$AG23="5"

Also, you don't have to do Conditional Formatting column by column, resulting in so many rules

if you do want to clean it up, go to the Conditional Formatting Rules Manager & select Show Formatting Rules for : "This Worksheet". Delete all of them & start over. you just need 5 rules for your 5 colours.
1. select from L23:W8605
2. go to Home -> Conditional Formatting -> New Rule -> Use a formula to determine which cells to format -> Format values where this formula is true:
=AG23=5
3. format colour

repeat the steps for AG23=4 to AG23=1

**AMSBenji** · 05-02-2013, 08:43 AM

benishiryo,

I know I added to your reputation, but I wanted to publicly thank you for your help. Also thank you to the others that have helped me over the last few weeks.

Thanks again

This website is so valuable, a great learning tool, and awesome to just browse and discover!