Colouring areas depending on column values

Dear Excel Forum,

I have spreadsheet with different data areas separated by a row, where I'd need to fill the whole area with different colours, depending on the values of column D (per area). If the values are all exact same, the area should be coloured green, if not, it should be coloured red.

Please also note, on column E I have marked X's next to lines. This X means the line shouldn' be used in the comparison, but should still be coloured aswell.

If there is only one row in an area (meaning nothing to compare to, excluding rows with X), the area should be coloured green by default.

Attached an example spreadsheet. On the Desired outcome -tab I have deliberately bolded the rows to indicate which values were used for the comparison (the macro does not need to do this)

Example_2.xlsx

Here you go

Please Login or Register  to view this content.

Code is sutitable for Excel 2003, will be OK in 2007 on
If it works for you, please click the star. If not I'll try harder.

One more thing: I found a slight bug, if an area has every line marked with X, it sort of combines itself with the area below, even colouring the empty row between them. What do I do?

Bump, here is an example of the error;

11249615.jpg

edit: doublepost by accident

Here is corrected code; I think I understand the requirement better now after losing the code 3 times!

Please Login or Register  to view this content.