I want to highlight column cells using a formula with conditional formatting in Excel.
More specifically I need help with a formula that highlights all cells over columns C1 to H3 if the values are greater than values in A2 and smaller than values in B2).
The following formula gives a correct formatting as long as the values in "Start" and "End" is between either ’18-20′ or between ‘1-3’. The formula is of course applied to the full range of cells from C2 to H4.
=AND(C$1>=$A2,C$1<=$B2)
Now, if the "Start" value is ’19’ and the "End" value is ‘3’, no cells will be highlighted.
This image shows what I want to achieve:
I suppose this is a fairly easy task for all the excel pros out there!
>Solution :
Try this formula for your condition:
=AND(COLUMN()>=MATCH($A2,$A$1:$H$1,0),COLUMN()<=MATCH($B2,$A$1:$H$1,0))
Trick is to compare the column index of the headers – as your figures are not consecutive.
MATCH returns the index of Start and End.
COLUMN() returns the index of the current cell.
