Maintain index table to show allocated numbers

[Aussiebear]

I have a workbook with a series of tables, each of which contains an allocated index number for each GPS mark therein. I can foresee that over time as new marks are entered in the workbook, I will need a method to maintain the next available number to use from a set range. Rather than visually scan up and down the column A data, I've created an index table to try and simplify to task, and this table contains not just numbers but coloured interiors to signify which GPS marks have an allocated Index number and are either allocated to a Lowrance plotter, or Simrad plotter or not uploaded onto either plotter. I am hoping to count cells by interior colour. From another thread I've been advised to manually force a recount, which I think I shall do by way of a button. Am i on the right track here?

[Aussiebear]

What I am now looking for is a method to match the interior colour of the cells in tblIndex (K4:AD66) to those index cells in Column A., by using a worksheet change event. Can someone assist me please?. Will be unavailable for a couple of days as I go in for surgery on both eyes today, so no need to rush any replies.

[Bob Phillips]

Colour change doesn't trigger an event Ted.

[SamT]

Ted, you really should combine these two threads. I know that there are two questions, but the subjects are very closely intertwined.

I have tried many ways to force the CountbyColor functions to automatically operate reliably and smoothly. I think that you really are going to have to use a command button or set aside a few cells to trigger the action when selected. At that time I think that I would do all the counting in VBA and just assign the results directly to the "answer" cells.

There are two macros available from the Tools menu. One synchronizes the Cells in Column A to the Cells in tblIndex. The other uses the cells in Column A as the master and changes the color in tblIndex.

I added a function that offers to put the next available unused index number in any empty index cell in column A whenever you select that cell in "A." See the Sheet VBA Express notes.

If you change the color of an index cell in column A, you can double click on it and it will change the corresponding cell in tblIndex to change colors to match.

[snb]

See the attachment

[SamT]

@ snb,

I like your function, simpler and suggests a way to speed up the one we've been using.

The bad news; I, at least, still have the same problem triggering it. Even using a cell formula and changing its inputs doesn't always trigger F_CBC. The only method I have found that always works is to delete a cell.




I found your white Easter egg

[Aussiebear]

I am now using a helper column to count the Lowrance, Simrad and non Allocated. I am still however looking at matching the colours of the index numbers in column A to those in the tblIndex. I am also limited to the amount of time I can spend looking at monitors, so if you already provided the solution please bear with me.

[SamT]

Goood to seee you back.

How are your peeepers feeeling and doing?

[Aussiebear]

Not to bad I guess, I can feel the stitches rubbing on the back of the eyelids and am very hesitant about sunlight. It is after all only the second day.

[SamT]

Ouch. Don't scratch. Wear sunglasses.

Quit staring at a monitor.

[Aussiebear]

ROFL....................

[Aussiebear]

I am so over this.......... I've even turned the screen intensity well down, but still find I'm trying to see through a watery blurry vision.

[SamT]

Take your time.

I'm leaving wednesday and

won't be back before December.