Hi,

Please supply your workbook and data and a clearer explanation of what it is exactly you want to happen.  

Please supply as much information as possible e.g. :

1. How does your data auto-update?

2. How do you want to 'chnage columns'?

3. What 'certain data passes'?

4. etc

Regards

Phil

Hi,

 

I cannot attach the full spreadsheet but have attached a snippet.

 

So i have the pivot table on the left where the "completed" column auto updates through a SQL macro everyday from an external source, that dosent really matter.

 

For example, i have a formula "I11-C11" to get a value for completed in that certain date. 

 

But what i want is that when that certain date passes, that column "I" automatically change from the formula to a fixed value that wont change.

Its still not particularly clear as the we cant see the actual workbook, but something like this might work.

You need to create this sub in the Thisworkbook module in the VBA editor. Then when you open the work book it will check if the date in I2 is less than today and if so copy the data to text in the whole of column I

There are a couple of issues you might need to adjust for 

- Date formats can be a little painful in VBA, i am in the UK so we use DDMMYYYY which is what i have set mine for.
- I dont know where the date is in your worksheet or if it will change so you might need to adjust I2
- I guess that column I will need to change to J the next day/week/month?

It is possible to get around the above, but i would need to see the workbook to be of more help.

Private Sub Workbook_Open()

Dim R As Range

Set R = Range("i:i")

If Format(Now, "DD/MM/YYYY") > Format(Range("i1"), "DD/MM/YYYY") Then
R = R.Value
End If

End Sub

Thanks Purfleet,

This formula works great. New to macros here, is there a way to loop this instead of copying this for each column you want to apply this to?

For example, so I wouldn't have to do something like this for each column:

Private Sub Workbook_Open()

Dim R As Range

Set R = Range("i:i")

If Format(Now, "DD/MM/YYYY") > Format(Range("i1"), "DD/MM/YYYY") Then
R = R.Value
End If

Set R = Range("j:j")

If Format(Now, "DD/MM/YYYY") > Format(Range("j1"), "DD/MM/YYYY") Then
R = R.Value
End If

End Sub

Many thanks

 

UPDATE, used the following formula:

 

Dim cell As Range

On Error GoTo ErrorHandler

For Each cell In Range("i1:u1")
If cell.Value < Range("i11") Then
cell.Offset(4, 0).Value = cell.Offset(4, 0).Value
End If

Next cell

Exit Sub
ErrorHandler:
Exit Sub

End Sub