For each cell... look at a range of cells not individually

[terratushi]

Hi,

The below code allows a user to double click a cell and the range below that cell called (varrange) will turn grey if there is a number in the cell, and the value of each cell is then copied to another table.

What I'm having trouble with is that if ALL the cells in the range (varrange) are already coloured grey (14013909) then I'd like to make the cell colour none and also remove the contents of the other table. The problem is that it looks at one cell at a time, I'm not sure how to look at all the cells in (varrange) before moving onto the next part.

Please Login or Register  to view this content.

Any ideas or input would be great, thanks

[TMS]

Gut reaction is that you'd need to loop through the range and count the grey cells and compare that to the varrange.Cells.Count. If the counts are equal, you can clear the colour and range, etc.

[terratushi]

Aah, that makes sense! Any ideas on how that would actually be coded? I'm still very new to VBA! Thanks again

[xlnitwit]

Perhaps this

Please Login or Register  to view this content.

[TMS]

Something like:

Please Login or Register  to view this content.

[terratushi]

Hi both,

I couldn't get the first option to work I'm afraid. The 2nd option looks more promising, but one issue...

The No GREY cells part works fine. However even when all the cells are grey, I still get the "some grey cells", rather than "all grey". To explain further, please see the below image:
Capture.PNG

1st column : No cells - WORKS
2nd column : All cells are grey (but says only some grey cells) - Not working
3rd column : Some grey cells - WORKS

I'm not sure if the reason why the 2nd column is saying only "some cells" is because the number of cells with a value doesn't match the size of the range? I'd only want to include the cells with a value in the comparison... hopefully that makes sense?

the "varrange.Cells.Count" part counts ALL cells in the range, whereas I think it needs to count only cells with a numeric value.

Thanks again

[xlnitwit]

A workbook would make it much easier to provide a solution, as would a precise explanation of what is needed. The is the first time I can see that you have mentioned only populated cells being relevant- which is probably why what I suggested doesn't work for you.

[terratushi]

Hi guys,

Sorry about that, I had been working on it for a while so when I explained my problem I didn't realise I had overlooked another part of the code which would have explained the numeric cells.

I've managed to get it to work by using:

Please Login or Register  to view this content.

Thanks again for all your help and ideas

[xlnitwit]

I'm pleased you got it working!

[TMS]

You're welcome.

I was counting grey cells, not numeric cells.

... is that if ALL the cells in the range (varrange) are already coloured grey (14013909) ...