MACRO of duplicate file

[vijay]

Hi friend,

The below query of my was solved by "Tom Hutchins". It would be helpful if
Tom answer to this question or someone else who understands me.
Tom has given me a written Macro for Excel for my daily work which given
below. but it still need some more inputs in it.
The problem is that i key number of file in column "I",the file which is
been keyed early is also keyed twice/thrice or more then thrice a day. Which
i don't come to know. so what i want is whenever the same file is repeated in
column "I" at any given row in column "I" that file should show up in
BLUE/RED color so that i come to know that this file is duplicate and i do
not need to work on it.
It would be helpful if someone help me on this with given macros below and
solve my above query with modifying that macros.
Thanks
Vijay



i am working with a organisation.where i need to work on a Excel Sheet daily.
It would be helpful if you get me done this, as it would save my lot of time
and would decrease my work load.
Problem as follow. Version (Excel 2003)
A B C D E F G H I J K L M N
1 I don't want macro to work on first 5 rows
2 I don't want macro to work on first 5 rows
3 I don't want macro to work on first 5 rows
4 I don't want macro to work on first 5 rows
5 I don't want macro to work on first 5 rows
6 A B C D E F G H I J K L M N
7 ST ET Y Z
8 ST ET Y Z
9 ST ET Y Z

The above A,B,C, are the Column, and to the right side rows with numbers.
Daily i do no. of file, which needs to be update in the Excel.
In column "F7" i have keyed "ST" that is equal to start time, before
starting with each new file i need to key the Start time in column "F", and
after completion of that file i need to key end time in column "G". In Column
"G7" ED stands for end time. Rest of the column are having other details that
i need to fill in.
Daily i do huge no. of files, for which i need to key the time everynow and
then.
What i want is whenever i Key "Y"=(file name) in column "I7" the cell in
"F7" should automatically pickup the current time of the system as "Start
Time". And after completing of that file, when i key "Z"=(file name) in
column "K7" the cell in "G7" should automatically pickup the current time of
the system as "End Time".
I don't want to use shortcut keys for the current time every time.
It would be helpful if you get MACRO written for me on this.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row < 6 Then Exit Sub
Select Case Target.Column
Case 9: 'column I
If Len(Target.Value) > 0 Then
Cells(Target.Row, 6).Value = Time
Cells(Target.Row, 5).Value = Date
End If
Case 11: 'column K
If Len(Target.Value) > 0 Then
Cells(Target.Row, 7).Value = Time
End If
End Select
End Sub

--
vijay

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...6bda5f5&dg=microsoft.public.excel.programming

 

[Brotha Lee]

Vijay,

You could use conditional formatting to solve this issue:
1) Select column I entirely
2) Goto format-> conditional format
3) Select Formula and enter the following formula:
=COUNTIF(I:I;I1)>1
4) Select and color coding you would like if the file exists more than 1 time

Regards,
brotha lee

 

[vijay]

Brotha,

Thanks for that. but the given formula is not working.
Example:-
This is the file name that i key in column "I" VH_PROEDE_AREDEDE_12_1345838.
If i have keyed the above file in column "I10" and again i key the same file
in column "I67" . Because in between i have keyed number of different files,
so i don't really come to know that the particular file
"VH_PROEDE_AREDEDE_12_1345838" as already been keyed in column "I10". so i
keyed the same file again in column "I67" which is of no use.
This happens daily for number of files, and more then 2 or 3 times duplicate
files are created. To avoid that i need to know that if the file that i have
keyed early
should show up in BLUE/RED color. There are number of files that i work on
daily, an each file with different name.
Please help out me on this. Can a macro be written on this.
and again thanks for the formula