Conditional formatting of cell range with non-consecutive values

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!

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.