Issues with Conditional Formatting Cells dependent on VLOOKUP

[AnthonyCappuccio]

Hey Everyone,

I have been having enormous issues trying to resolve a conditional formatting problem within an Excel Sheet because the cells referenced in the rules I create are dependent on the VLOOKUP function.

I am running Excel on Microsoft Office Professional Plus 2010. I am attaching a "dummy" file that I put together so anyone could play around with the file to get a better idea of what I am trying to do.

.

.

Tab: IP

- Applicable field is the "SELECT PART#" as this is used in my VLOOKUP function

Tab: INSP

- Row 10 is of interest here, specifically the columns of K and L.

- E, F, G will be auto-populated based on the selected part number from the IP tab

- K and L are where the conditional Formatting lies. See "KEY" tab for more on what the three rules are for the conditional formatting.

Tab: DATA TABLE
- Values from this table are the ones being pulled using the VLOOKUP function. Specifically, columns 7 and 8 are important because these columns are referenced directly in the conditional formatting.
Tab: KEY

- This tab highlights the generic form of the VLOOKUP function I am using. The explanation on the right describes what to change within the formula to adjust for a separate column on the "DATA TABLE" tab (highlighted in blue on said tab).

- Additionally, the Goals of the Conditional Formatting are laid out with their generic forms and reasoning for what each rule should be doing

QUESTION (Please refer to excel file for specifics):

On the INSP tab:

The rules for the conditional Formatting are not working how I would like them to. I have them set up to compare the result within the Min/Max column to the Lower/Upper Limit that is auto-populated based on the Part Number chosen on the IP tab. Basically, if the value inputted into Min/Max column is OUTSIDE of the Lower to Upper Limit range; I need the Min/Max cell to format red. Conversely, if the value is INSIDE this range; I need the cell to format to white (no-fill).

Since the Lower/Upper Limit cells contain a formula (VLOOKUP), I believe this is breaking the rules I create and causing any value inputted in the Min/Max section (whether inside or outside of Limit Range) to constantly format the cell red.

.

.

I have exhausted so many hours into messing around with the conditional formatting and research online through various websites and help forums, I do not know what else to do. Please Help.

[#2]

@AnthonyCappuccio 

Rules should be ordered this way:

Yellow: =ISBLANK(K$10)  STOP IF TRUE CHECKED

White: =AND(K$10>=VALUE($F10),K$10<=VALUE($G10))

Red: =OR(K$10<VALUE($F10),K$10>VALUE($G10))

[#3]

@Patrick2788 Thank you so much for the response. 

I have a follow up question if you don't mind:

I would like to apply these set of rules to a large range of rows (roughly 100), is there a way to "roll" this rule down along multiple cells or would I have to input the rule into each individual cell? The only change in the rules from row to row would be the row number (in the case of your post and my example sheet, the number 10 would be the item in the equations you posted that would be changing per row).

I say roll with quotations because I know excel has the feature that if you type a number into a cell, say 1 for example, and drag the little black box in the bottom right corner of the cell and select "Fill Series" it will chronologically list from the number 1, automatically into the corresponding cells. I have been using the brute force method of inputting into individual cells but obviously that is time consuming and tedious so I would like to avoid doing this if possible

[#4]

@AnthonyCappuccio 

Go into manage rules and adjust the applies to box.  Included a screen cap for reference (These aren't using your rules. Just a sample).

If you don't extend conditional formatting this way the other ways are using a table or the format painter.  The latter has a few nuances.  For example, if A1:B1 contain conditional formatting rules, you select those cells, click format painter, then brush the cells to extend - let's say A2:B10.

[#5]

@Patrick2788 

Thank you for the feedback Patrick. Using the 'Applies To' section will not work for my case because the rule itself would still be checking cells F10 and G10 (I am referencing the equations you wrote out as the original response on this post).

By rolling the rule downward to apply to more cells, I would additionally need the mention of F10 and G10 to roll as well (i.e. one row down the rules would need to change to F11 and G11). I know this is very specific and having the rules themselves actually change is the part that may very well be obtainable in excel, wanted to put it out there just in case.

Also, thank you again on the equation formatting. That really was a huge help for me.

[#6]

@AnthonyCappuccio 

Conditional formatting with a formula and a range of cells is built with the first row in the range in mind.  As long as you use correct referencing styles the references to F and G should change accordingly by the row.