I want to be able to apply conditional formatting quickly to rows in a series of tables in Excel (using Excel 2007 currently) to pick out the maximum and minimum value in each row. It's easy enough to do it a row at a time: simply select the row, Click on Conditional Formatting, pick Top/Bottom rules, pick Top 10 items, change the 10 to a 1 - and then repeat the process for the Bottom 1 item. Slow.
But if I try to copy & paste-special formatting to remaining rows in what might be a long table, then it picks out the top and bottom for the whole block - not what's wanted.
As it happens, I think I can figure out a way to do it with a VBA macro (so you'd select the block and run the macro via keyboard accelerator) but I prefer not to expose team members to VBA for all sorts of reasons. So ideally I'm looking for a "formula" that I can put in the "new rule" dialogue. I figure the formula (if it can be devised) would, for each cell to which it applies, test its value against the MAX (MIN for a second rule) of all values in the current row - where rows are as wide as they happen to be for that block of data.
I've been boggling at combinations of INDEX(), INDIRECT(), ROW(), and I keep thinking I've almost got it, only to find I'm stumped again.
Any ideas, or is it impossible?
Remember when writing conditional formatting on a block of data, that you are actually writing the formula exactly as shown in the formula bar for the top left cell of a data block. Every other cell is the same formula as if you'd copied and pasted that formula down/to the right as needed. So that means that you look at absolute references ($'s), and relative references (no $'s).
For example:
In a datablock B2:D10, we want the max & min of each row highlighted. If we were going to write this formula for only B2, we'd say [remember, the formula is to be written so that it either says TRUE, meaning the conditional format applies, or anything else, and then the format does not apply]:
=OR(B2=MIN(B2:D2),B2=MAX(B2:D2))
If B2 is equal to either the min or max of that row, the rule will resolve as TRUE, and the formatting will apply. To extrapolate that for the other cells, now look at what you want to 'stay the same', and what you want to 'change', as you copy this formula down and to the right:
=OR(B2=MIN($B2:$D2),B2=MAX($B2:$D2))
In the above, we always want the 'B2=M...' to change in the row and column - because that will apply the rule individually to each new cell. We also want '$B' to stay the same, because the columns you are comparing with dont change [you are always comparing with column B - D]. We do however want the row of the checked ranges to change, as the row changes as you drag down.