Forum Discussion
Excel auto fill gantt chart by 15 min interval
AH you need the rounding to include those partials. I see. You just need to offset that start time
Try:
=AND(I$1>$C2,H$1<=$E2)(but still applied to $H$2:$CZ$9 or whatever the end is)
That formula seems correct assuming the RANGE the conditional formatting is applied to is $H$2:$CZ$2 (the formula must be correct for the top left corner of the Applied To Range)
If you want row 9 then replace the 2 with 9 or better yet if you want all the rows from 2 down to 9 then then just use that SAME formula and make the Applied To Range:
$H$2:$CZ$9 (notice again the top left corner is still H2 and so will work)
i will only highlight the cell for 0530 instead of from 0515
AH you need the rounding to include those partials. I see. You just need to offset that start time
Try:
=AND(I$1>$C2,H$1<=$E2)(but still applied to $H$2:$CZ$9 or whatever the end is)
- it works thanks!
do you mind explain abit about the formula?- glad it works. The way conditional formatting works is that it starts in the very upper left cell of the 'Applied To' range and checks the formula. So in this case H2 will be True (i.e. special formatting applied) if the value in cell I1 is > the start time AND the value in H1 is <= the end time. Notice how there are $ in front of some of the letters/numbers and not others? When a $ precedes a letter or a number it means to 'lock' that letter or number while a letter or number without a preceding $ means it is relative.
SO when excel goes to the next cell I2 then the formula it will check is ... AND(J$1>$C2, I$1 <=$E2) basically the I and the H of the formula do NOT have a $ before them so when excel looks at the next column it also moves those cells to the next column also. Similarly when excel goes to the next row the number 2s will become number 3s but the number 1s have a $ in front so they will stay $1.
That all said, basically the formula LOCKS the reference to the 1st row but moves left and right accordingly and LOCKS the reference to the start and stop time columns but allows to move down to stay on the same row as the cell of interest.