Trace all the changed cells range...

[Sridhar Pentlavalli via OfficeKB.com]

Hi

Can any body help me how to idenify the changed range of cells.

If cells D14 are selected and deleted then cells E1:E4 should also be deleted.

I am using the following code for it.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
changed_row = Target.Row
changed_column = Target.Column
' Do the process
End Sub

But with the above code, I am able to trace only first cell D1 and I am deleting E1. How to put it in loop to trace all the changed cells range.

Thanks in advance
Sridhar P

 

[Tom Ogilvy]

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
if Target.Column.count = 1 and Target.Column = 1 then
Application.EnableEvents = False
Target.offset(0,1).ClearContents
End if
Application.EnableEvents = True
End Sub

--
Regards,
Tom Ogilvy

Hi

Can any body help me how to idenify the changed range of cells.

If cells D14 are selected and deleted then cells E1:E4 should also be deleted.

I am using the following code for it.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
changed_row = Target.Row
changed_column = Target.Column
' Do the process
End Sub

But with the above code, I am able to trace only first cell D1 and I am

deleting E1. How to put it in loop to trace all the changed cells range.

 

[Sridhar Pentlavalli via OfficeKB.com]

Hi Tom,

Thank you for the help. I am getting the error

"Compiler Error"
"Invalid Qualifier"

and "Column" is highlited in the expression "Targer.Column.Count"...

Can you help me ....

 

[Tom Ogilvy]

if Target.Column.count = 1 and Target.Column = 1 then
should be

if Target.Columns.count = 1 and Target.Column = 1 then

make the first column plural (columns)

 

[Sridhar Pentlavalli via OfficeKB.com]

Hi Tom

That is excallent.... It is working now... Can I trace the row numbers also....? Here is my exact requirement....

I will be having a sheet with 7 columns out of which first 5 columns are unprotected. 6th and 7th columns are protected. Now when ever user deletes any of the value in row I, i should blank the cell (I, 6) and put "Y" in cell (I, 7). If user deletes one value in one cell ... then its ok.... I can manage .... but the intelligent user is selecting say, from (2,4) to (6,4) and deleting. Then for all the rows from 2 to 6, 6th column should be emptyed out and 7th column should have "Y".

Can you please help.......?

 

[Tom Ogilvy]

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim rng as Range
Application.EnableEvents = False
' check if cells have been cleared
if application.CountA(Target) = 0 then
' Activesheet.Unprotect Password:="ABCD"
set rng = Intersect(Target.EntireRow,Columns(6))
rng.ClearContents
rng.offset(0,1).Value = "Y"
'Activesheet.Protect Password:="ABCD"
end if
Application.EnableEvents = True
End Sub

--
Regards,
Tom Ogilvy

Hi Tom

That is excallent.... It is working now... Can I trace the row numbers

also....? Here is my exact requirement....

I will be having a sheet with 7 columns out of which first 5 columns are

unprotected. 6th and 7th columns are protected. Now when ever user deletes
any of the value in row I, i should blank the cell (I, 6) and put "Y" in
cell (I, 7). If user deletes one value in one cell ... then its ok.... I can
manage .... but the intelligent user is selecting say, from (2,4) to (6,4)
and deleting. Then for all the rows from 2 to 6, 6th column should be
emptyed out and 7th column should have "Y".

 

[Sridhar Pentlavalli via OfficeKB.com]

Hi Tom

Lots of thanx for the help.... It worked and now my application became very flexible and all those (my) client appriciations goes to you....

I have an another problem in my project which I posted on 29 Dec 2004 in the same forum and eagerly waited for any help.... but unfortunately no reply ... Can you just have a look to help me...

Here is the link to the problem...

http://www.officekb.com/Uwe/[email protected]