Can anyone help me with a sequentially colour coded tab?

Hi,

I already have some VBA that will set the tab colour based on content in 5 key cells. The problem is that it isn't sequential, so if someone completes cells 1,2 & 5, the tab changes to the colour for cell 5, whereas I need the tab to be the colour for cell 2. So the tab colour should always sequentially represent the number of steps completed, even if at some point, one of the steps is undone (cell contents deleted).

I'd appreciate any push in the right direction. I don't know very much about VBA at all.

Snippet attached.

Thanks,

Tow

Hi Special-K,

Thank you for your suggestion.

I have a working model based on spreadsheet formulas, which I am much more familiar with than VBA. In my model, I can sequentially change an "indicator" cell from 1 to 5 based on the number of steps completed. If someone completes steps 1, 2, 4 & 5, for example, the number will show 2, because step 3 is not complete. As soon as step 3 is complete, it will show 5.

I can also change my tab colour according to numbers 1 to 5 entered into a cell directly, so you would think I have the full solution but my VBA doesn't activate unless I enter the value into the cell directly. My formula method above, does not trigger the VBA.

Either I need to find a way of combining the two, or I have to find a way of completing the equivalent in VBA.

Tow

Okay,

So I have cobbled together a solution but I'm sure it could be more efficient.

My solution is a combination of spreadsheet formulas and VBA. The spreadsheet formulas allow me to detect content in 5 dispersed cells and create a sequential output in a single cell, with a value of 1 to 5, representing the steps completed.

The VBA looks at the single cell (G7 in my model), and sets the tab colour according to a set of value/colour combinations.

I think if I could select the 5 cells for Target.Address in one go, I'd feel happier but I can't find the syntax that will let me select 5 dispersed cells as opposed to a block of cell, which seems to be far easier.

I've attached my script for comment.

Once again, any help appreciated.

Tow