Macro to colour rows needed

[chuckles1066]

Hi all,

I've tried to adapt something that I picked up here previously but to no avail.

I'll keep it simple, I think I'll be able to modify whatever solutions you guys are able to come up with.

So.....column A contains date, column B contains time and column C contains site ID.

Starting at the first row of data (row 2) format the row from columns A to C with a colour (say purple).

Now the fun begins......

For each subsequent row, the macro should

(a) check to see if the site ID matches the one above
(b) if it does, is the time in that row less than 2 hours from the row above
(c) if so, then the row needs to be coloured as the row above
(d) if not, or if the site ID doesn't match the one above, the macro needs to do nothing, move onto the next row and repeat the procedure

Any advice greatly appreciated as always.

(PS: if anyone's feeling especially brave, at some future point I'll have to build in something to determine the 2 hour thing when the date is different; e.g 23.00 hours on a Friday to 00.05 hours on a Saturday is less than 2 hours but my existing macro won't pick it up because if you subtract 23.00 from 00.05 the maths doesn't work).

 

[GlennUK]

Compare date plus time to get your maths working. You say that you have a macro already ... is it that you can't get it working properly? Maybe you could show your code if that is the case.

 

[venkat1926]

I perhaps did not understand the full implications of your needs. can you work on this.

see the formula in the sample sheet in column D and its conditional foramtting

Excel Workbook
	A	B	C	D
1	date	time	id
2	4/1/2011	7:00	a
3	4/1/2011	10:00	s	0.13
4	4/1/2011	11:00	d	0.04
5	4/1/2011	23:30	f	0.52
6	4/2/2011	0:40	g	0.05
Sheet1

the conditional formatting is for D3 is
the formula is
=D3<=2/24
and this format is copied down the column D(pastepscial-format)

 

[chuckles1066]

Thanks both for the input but I have (finally!) managed to tweak my existing macro.