Help 'freezing' value of formulas when date becomes 'today'.

[Big.Moe]

Hello,

I already have this handy code that 'freezes' the current value of a formula when there is activity in the cell adjacent.

Please Login or Register  to view this content.

Now I would like to do a similar process for the "Item Description" column. This time I would like the formula values to 'freeze' when the associated date for that day becomes the current date.

Freeze Column.jpg

Regretfully, the column letter always changes, and the date cell always changes. However, they always have the same juxtaposition (see attached file of typical month sheet below). Sorry, could not upload whole workbook due to size constraints.

My VBA skills are next to nothing, but this is the general direction of what I think the code should look like:

Please Login or Register  to view this content.

Thanks for reviewing my post and hopefully I've clearly described the problem at hand. Not always the easiest task to master.

[antoka05]

You could use this code to put in worksheet change:

Please Login or Register  to view this content.

Regards,
Antonio

[Big.Moe]

Hi Antonio,
Forgive me, but I'm having trouble getting your code to work. This is how I inserted it into my existing code:

Please Login or Register  to view this content.

Is this correct? My apologies, but I was not exagerating when I mentioned my VBA skills were next to zero.

Couple Questions:
"If Target.Parent.Cells(8, Target.Column) = "RETAIL" Then"
Does the "8" denote a cell reference? If that is the case, I think it might need to be a "40" because that is where the "Retail" call-outs begin on the sheet.

"If Format(Target.Parent.Cells(2, Target.Column + 9)"
What does the "2" represent in this part of the code?

Does the code automatically change the formulas to values when the current date matches the sheet date?

Thanks.

[antoka05]

Excuse me but I realized a different ask.

Try with this code:

Please Login or Register  to view this content.

Target.Parent refers to the sheet, for example Target.Parent.Cells(2, Target.Column + 9) refers to the date that is 9 columns right the column with Category at row 2.

Regards,
Antonio

[xladept]

Hi Big Moe,

I'm interested in this thread and I'll try to help tomorrow

[xladept]

Hi Big Moe,

It took quite a while before I realized that you had the date in a merged cell which made the offset just be 9!

Please Login or Register  to view this content.

[Big.Moe]

Thank you to both of you for your suggestions.

For some reason, xladept's code is the one I got to work best, but I certainly appreciate both your efforts, and a little rep will go out to both of you.

That "Application.EnableEvents = True/False" thing is pretty cool stuff. I thought the code would 'freeze' the values as soon as I open the file, but it waits until some activity happens which is even better. I'm going to read-up on "Application.EnableEvents = True/False" to better understand it.

Thanks again for the help. What would we do without you forum gurus/experts?.....Probably toil away in our pedestrian Excel ineptitude.

Oh, and my apologies for the sneaky merged cell.

[xladept]

Hi Big Moe,

The code differs mainly in the fact that I used the selection change event (forgot to mention it).

I should have read Antonio's code as he noticed the merged cell and was offsetting by just 9!

You're welcome and thanks for the rep!