Is there a way of using colour/color fill to activate a function?

[tasmith7482]

I am a teacher who wants to create a linear progress spreadsheet that is easy for other staff to use. I want the interface to be so that the teacher simply needs to fill a box with colour and the grade will come up in an end box. Is it possible to do this?

Also I want it so that each new fill would over ride the previous, ie if first D3 is filled with colour a grade 2C will come in the outcome box but when D4 is filled a grade 2B would appear as the child has progressed and so on up to D12.

I hope this is not too confusing but it is doing my head in!!!!

If the answer is, "you can't do this" then fair enough

[mrice]

Please see the attached. It uses an event driven macro to work out the grade. You can see the code on the Sheet1 tab in the VBA editor (Alt F11).

The challenge here is to fire the code when a colour changes. As I couldn't see an obvious way of doing this, I've gone for firing the code every time a new cell is selected as this is a very commom occurence.

I hope that this make monitoring of Key Stage 2 a little easier.

[tasmith7482]

That has actually blown my mind!!!! How would I do it side ways? Here is an attachment of what I am trying to do with staff with the function filling in box so that the outcome would arrive in the Q column?

hope this makes sense.

PS. I already donate to cancer research for family reasons.... this will be upped this year!!!!!

[tasmith7482]

please se first athletics tab for best example

[mrice]

Please see the attachment.

Having the example helps. I can see that its not just the colour but also some text which is going into the cell. This enables us to use a much better trigger which fires when a cell changes and allows to code just to run on the changed row(s).

I've moved the code to the ThisWorkbook tab so that it runs for all sheets.

I've also taken the liberty of changing the formulae in the final tab. The reason for this is to enable you to directly look up the name so should still work if the names get out of order or extra header rows are added.

The one thing that you need to watch out for is that there must be a cell in column A on each sheet containing the word "Name" directly above the data. This cell is used as a reference point for the code.

[tasmith7482]

I wish I had your brain.... I try to read your workings and can see the logic but cannot fathom how to write it myself..

2 questions, level 5's seem to come up as 4's still.... is this me doing something daft?

Also the "running" page does not seem to reflect the pages when they change ie. athletics still has 1's and 2's despite chn being 4's and 5's?

I am so grateful for your help.... My head is like a wasps nest with excitement and confusion.... I am a Y1 teacher with a background in Drama this is not really my forte but it inspires me to want to learn more!

[tasmith7482]

I fixed the 5 thing!!!!!!!! I alterec the VB code! probably the most simple thing ever but you wouldn't believe how proud I am of my self for finding the relevant piece of info and changing 4 to 5.....

simple pleasures!

[tasmith7482]

Sorted other issue too.... all formulae said Art, I changed each to the relevant sheet.

I am getting too carried away now!

If I added new tabs ie. History, RE etc Would VB automatically work now?

[mrice]

Well spotted and fixed. New tabs, as long as they conform to the same overall format should work as well.

As an ex Chair of Governors at a local junior school and as the husband of a year 4 teacher, the data looks very familiar to me. I know know where to come for advice on drama .

[tasmith7482]

Feel free... although teaching breathing techniques and projection may be difficult on line... If any part of the spreadsheet is useful to your wife or her school make sure she uses it.... most of the hard work is, after all, yours. many thanks