Forum Discussion
Issues with Conditional Formatting Cells dependent on VLOOKUP
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))
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
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.
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.
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.