23 09 08.xlsm | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
D | E | F | G | H | I | J | K | |||
3 | Rate | Amount | Rate | Amount | Rate | Amount | Rate | Amount | ||
4 | 500 | 1,000,000 | 155.25 | 310,500 | 480.00 | 960,000 | 130.00 | 260,000 | ||
5 | ||||||||||
6 | 155.25 | 310,500 | 480.00 | 960,000 | ||||||
CF MAX MIN (2) |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
D4:K6 | Expression | =AND(D$3="Amount",D4<>"",D4=MIN(IF($D$3:$K$3="Amount",IF($D4:$K4<>"",$D4:$K4)))) | text | NO |
D4:K6 | Expression | =AND(D$3="Amount",D4<>"",D4=MAX(IF($D$3:$K$3="Amount",IF($D4:$K4<>"",$D4:$K4)))) | text | NO |
Thanks again.In case you may have (now or in the future) more columns than just D:K you could also use something like this which, although a little longer, is easily expandable since it is applied to the whole range (D:K in this case but could just as easily be D:Z or beyond), not just every second cell.
23 09 08.xlsm
D E F G H I J K 3 Rate Amount Rate Amount Rate Amount Rate Amount 4 500 1,000,000 155.25 310,500 480.00 960,000 130.00 260,000 5 6 155.25 310,500 480.00 960,000 CF MAX MIN (2)
Cells with Conditional Formatting Cell Condition Cell Format Stop If True D4:K6 Expression =AND(D$3="Amount",D4<>"",D4=MIN(IF($D$3:$K$3="Amount",IF($D4:$K4<>"",$D4:$K4)))) text NO D4:K6 Expression =AND(D$3="Amount",D4<>"",D4=MAX(IF($D$3:$K$3="Amount",IF($D4:$K4<>"",$D4:$K4)))) text NO