achin
04-20-2008, 10:53 PM
I'm new to VBA. Somebody help me please...
I have two files, A and B.
In file A, there will a report for us to update progress of project (in percentage) , which included many projects within the same worksheet.
In file B, it's only one of the project from file A.
What I need is, whenever there is update of progress in file A, file B will capture through Hlookup formulas.
When the file B has captured a 100% of task completed (column G), it'll display the date of when is the data input in file A at the column next to it(column F) .
The date captured must be static. I do not want it to be changed to current date whenever I run the macro or open the file.
How to do these two functions?
I only have code for part of it which is not analysing if the data input value is 100% or not.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 7 Then
Application.EnableEvents = False
With Target.Offset(0, -1)
.Value = Now
.NumberFormat = "dd-mmm-yyyy" 'put desired format here
End With
Application.EnableEvents = True
End If
End Sub
The attached files contained the files: A and B.
I have two files, A and B.
In file A, there will a report for us to update progress of project (in percentage) , which included many projects within the same worksheet.
In file B, it's only one of the project from file A.
What I need is, whenever there is update of progress in file A, file B will capture through Hlookup formulas.
When the file B has captured a 100% of task completed (column G), it'll display the date of when is the data input in file A at the column next to it(column F) .
The date captured must be static. I do not want it to be changed to current date whenever I run the macro or open the file.
How to do these two functions?
I only have code for part of it which is not analysing if the data input value is 100% or not.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 7 Then
Application.EnableEvents = False
With Target.Offset(0, -1)
.Value = Now
.NumberFormat = "dd-mmm-yyyy" 'put desired format here
End With
Application.EnableEvents = True
End If
End Sub
The attached files contained the files: A and B.