Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trace all the changed cells range...
Hi Can any body help me how to idenify the changed range of cells. If cells D1:D4 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 -- Message posted via http://www.officekb.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trace all the changed cells range...
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 "Sridhar Pentlavalli via OfficeKB.com" wrote in message ... Hi Can any body help me how to idenify the changed range of cells. If cells D1:D4 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 -- Message posted via http://www.officekb.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trace all the changed cells range...
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 .... -- Message posted via http://www.officekb.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trace all the changed cells range...
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) -- Regards, Tom Ogilvy "Sridhar Pentlavalli via OfficeKB.com" wrote in message ... 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 .... -- Message posted via http://www.officekb.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trace all the changed cells range...
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.......? -- Message posted via http://www.officekb.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trace all the changed cells range...
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 "Sridhar Pentlavalli via OfficeKB.com" wrote in message ... 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.......? -- Message posted via http://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
trace dependent tool doesn't work but trace precendent doesn't | Excel Discussion (Misc queries) | |||
Trace Precedents and dependents for a range of cell ? | New Users to Excel | |||
Lock cells, but still be able to trace | Excel Worksheet Functions | |||
Trace Dependents for range of cells | Excel Discussion (Misc queries) | |||
how do trace dependents for all cells at once? | Excel Discussion (Misc queries) |