I have a data sheet with min-max possible values adjacent to each other. I'd like to color the cells based on how close they are to matching (ie the color should be based on their difference) I currently have a conditional formula that colors all of the non-matching values red.
Over the range
B:M (Note: that there are 20 pairs for each row and hundreds of rows). But now I'd like to improve the sheet by making the color change based on how close the values are to matching. I've tried using Format all cells based on their values with formula based limits that depended on the adjacent cells but excel gave the error You cannot use relative references in Conditional Formatting criteria for color scales, data bars, and icon sets. I'm guessing that is so excel only has to calculate the color ranges once. I can't think of any workarounds that don't involve relative references.
The only thing I've currently thought of is to use a set of formulas like the one above that use set color values for particular ranges (1-5->green, 5-10-> lime,10-20->yellow, 20-40-> orange, 40+ -> red) but I'd prefer a more elegant solution that doesn't require entering 10+ rules to get the granularity I'd like.
Another possible workaround would be to insert a third difference column per pair and just color that one column, but then my data table would be too wide to fit on my screen, and I don't think I can apply a gradient color to every third column. Though the advantage of this method would be I could apply a logarithm to the difference to get the granularity where I want it. If there was a way to copy formatting from a another region I could make the colors in another section of the sheet and then dynamically copy them back to the data entry area, but I haven't found a way to copy formatting dynamically.