Count certain colored cells over 4 wide

Hi there,

I am working on a timeline sheet and have been stumped on doing something automatically if it can be.

In the attached workbook I have a line of data. In it you will see certain things in different colors. What I would like to excel to do is look at all the grey merged cells and any merged cell that is over 4 blocks wide report back how many extra cells wide it is. For example in U3 there is a grey merged block that is 8 blocks wide so this should count as 4 extra and in cell AE3 that merged block is 2 extra wide. I would like the function to work across a range that I define but for testing purposes D2 to DX20 would be fine and the total number of blocks over should be in U1.

If you require any further info please ask.

Thanks
Ian

The code runs through the list specified, counts the number of cells in the merge and writes the findings on sheet 2.

Change this part of the code. It checks for a cell with the color index = 48 which is the shade of gray in the example.

Please Login or Register  to view this content.

Hi There,

I have tried the code change you supplied and while it now seems to only count the grey squares the number it produces is not correct.

For row 3 the total should be 11 as in the example I have attached 4 extra squares U3 TO AB3 (Y3+Z3+AA3+AB3) 2 extra squares AE3 TO AJ3 (AI3+AJ3) and so on giving a result of 11 for that row, but if you look in sheet 2 the result is a lot more.

Thanks

I see what is happening, I'm counting every cell in the merged cell. So instead of just U3, I'm also detecting V3:AB3. I'll figure that out.

I come up with 10 instead of 11. You are telling me to count cell DB3, but this is only the third cell in the merged CZ3:DB3. The two cells before it are also shaded gray, but are a separate merge. Now if you want me to count consecutively shaded gray cells, that's another story.