after x amount of saves

[iisailor]

Hi everyone,
Just wondering if it would be possible in excel to have a macro that implements only after a sheet has been closed a certain amount of times. The idea is that we work on sheets with a large amount of data on them. These are constantly updated and accessed by different departments. Iv come up (with the help of users of this very site, thanks agian to all helpers) with a macro that changes the colour of cells as they are editted. Upon save this changes all the cells that have been editted to another colour again. The worksheets usually then accessed by another department and once they are finished the colour can be removed and the cells set back to default white. The problem however is, for example, when the worksheet is editted and saved. Then the next department opens it 10minutes before the end of the day. They dont get there work done in time and need to open it again the next day but at that stage all of the colouring has been reset by the macro.
I hope that wasnt too confusing.
Its not a major issue, just something to lighten the load.
Any help or suggestions greatly appreciated
Chris

 

[Smitty]

You can use something like this:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_BeforeClose(Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)<br>    <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>        <SPAN style="color:#00007F">Set</SPAN> ws = Sheets("Sheet1")<br>        <SPAN style="color:#00007F">Set</SPAN> rng = ws.Range("A1")<br>        <br>        rng.Value = rng.Value + 1<br>        <br>        <SPAN style="color:#00007F">If</SPAN> rng >= 3 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Call</SPAN> MacroName<br>        <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

 

[onlyadrafter]

Hello,

I can think of two options.

1) Keep a count in a cell somewhere, that increments by 1 everytime the file is saved. When it reaches 5 a macro can be called.

2) Ask the user if they want all cells to be returned to their original colour.

 

[iisailor]

Just a quick note to say abig thank you to both of you for your help. i used both ideas and the code so will let the powers that be decide for themselves which they prefer to use.
Genuine thanks again to both of you
Chris