Macro to convert cells to text or value based on date change

**simpson** · 01-07-2012, 03:47 PM

I need to be able to update the date and pricing on the pricing sheet daily. however, i would like the information on the previous dates sheets (AM6:AV35) to become text when i change the date.
For example;
on 1/1/12 all of the prices are put in as they appear in the sample and all if the information in the first worksheet dated 1/1/12 cells AM6:AV35 is accurate.
on 1/2/12 i change the date on the pricing sheet (J1) to 1/2/12. That will make all the data on sheet 1/1/12 (AM6:AV35) turn to text so that when i change the prices in the pricing sheet it will only update the remaining sheets.
basically any sheet that has a date less than the date on the pricing sheet will have (AM6:AV35) become text.
thanks in advance for your help.

**JBeaucaire** · 01-07-2012, 06:28 PM

Your code is password protected.

**simpson** · 01-07-2012, 11:32 PM

i think i removed it. sorry about that.

**im_kaushal** · 01-08-2012, 01:45 AM

Hi Simpson,

Please find attached workbook. I have created a change event code which should change the requried range into text.

jan(1).xlsm

let me know if that is what you needed.

Rgds, Kaushal
Delhi, India

**simpson** · 01-08-2012, 01:32 PM

That looks like it will work. It will take me some time to get it installed in the actual worbook but it appears to be exactly what I was looking for.
GREAT FIRST POST!!
thanks

**JBeaucaire** · 01-08-2012, 08:45 PM

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

**simpson** · 09-17-2012, 04:44 PM

I have been using this macro for some time with success. however, it does not run automatically like it does in the sample. It only works when i open the macros and run it. In the sample when i change the date on the pricing sheet the macro runs automatically. any ideas why this is?
In the sample it is the only macro. In my actual workbook there are several. is this why?

**JBeaucaire** · 09-17-2012, 09:47 PM

Macros in the sheet module with the correct macro names can run themselves when properly designed. Macros in standard code modules (Module1, module2, etc) do not run themselves. If you put a sheet-module macro into a regular code module, they will not run themselves, they are in the wrong place.

Any further commentary on what is actually happening in your workbook would require you to post that workbook so we can look.