Applying conditional format to INDIRECT referenced cells

[MiniBlueDragon]

Hi folks,

I have a spreadsheet which has dates in column A, times in column B and a numerical figure in column C.
I also have helper columns which have a figure for the start and end row for that section of data (see below)

I need to apply a Color Scale conditional format to the numerical figures across each day, for example:

DATE | TIME | FIGURE | startRow | endRow
01/06/2014 | 07:00 | 6 | 2 | 7
01/06/2014 | 08:00 | 9 | 2 | 7
01/06/2014 | 10:15 | 1 | 2 | 7
01/06/2014 | 12:00 | 0 | 2 | 7
01/06/2014 | 15:00 | 18 | 2 | 7
01/06/2014 | 19:25 | 19 | 2 | 7
02/06/2014 | 09:30 | 1 | 8 | 11
02/06/2014 | 09:40 | 6 | 8 | 11
02/06/2014 | 10:05 | 2 | 8 | 11
02/06/2014 | 10:25 | 10 | 8 | 11
03/06/2014 | 08:00 | 6 | 12 | 12
04/06/2014 | 09:00 | 42 | 13 | 13

In the above example I need the format ranging from 6 down to 19 and a new format from 8 down to 10.

I've created helper columns for the first and last row of each data set but I can't seem to use INDIRECT and the cell data in the "Applies To" section of the conditional format. Any ideas please?

The formula I'm trying is:

=INDIRECT("C"&D2):INDIRECT("C"&E2)

VBA is a possibility as there's already a lot of advanced VBA in the spreadsheet; I was just hoping to save time with a straight forward "apply the format to these cells" via INDIRECT if it's possible?

Thanks folks.

[MiniBlueDragon]

Hmmm... I take it I've asked for something impossible then?

[Malkier]

MiniBlue,

If I understand you correctly I think you mean from values 6-19 and 1-10? not 8-10?

I can't visualise the conditional formatting option you are talking about but could you use the date itself as the condition? This of course is limiting as you will need as many rules as there are dates...

Regards,
Zac

[MiniBlueDragon]

Yes that's correct:

The date 01/06/2014 has 6 entries with values 6, 9, 1, 0, 18 and 19 as an example and those values need a Color Scale applied to them.

The next date of 02/06/2014 has 4 entries with values of 1, 6, 2 and 10 and that requires a new Colour Scale being applied to it.

The "rule" for the data set is Colour Scale so I believe it's only the "Applies To" field that I can edit which is why I thought perhaps INDIRECT would work as I can define the row number for the start and finish for that data set:

=INDIRECT("C"&D2):INDIRECT("C"&E2)

I could create a rule based on the Date as you suggested but as each day the entire data set is updated I'd have to manually update all the color scales daily anyway (and if I'm doing that I may as well just select the cells and apply it manually rather than doing it programmatically)