PDA

View Full Version : Change in a cell text, inserts an X in another cell



sithelo
03-15-2012, 01:53 AM
I would like help where if I change a cell text not just a click from worksheet_change event, automatical an X is inserted on the offset cell .

lncrj017
03-15-2012, 02:04 AM
attach your sample file so that we can understand clearly thanks

sithelo
03-15-2012, 02:28 AM
Please find attached an excel spreadsheet. I have labelled two tables. Table A is changed to Table B. I have highlighted the changes in Table B.
What I am interested is when there is change in table A as highlighted in a copy (Table B) if its description change it has to be shown under the Descr Change with letter S. If its Property column that has changed it should be shown in the Property Change column with letter R.

lncrj017
03-15-2012, 07:45 PM
download the attached file and try to run goodluck



Sub Sample()
Dim i, c, lrs, lcm As Long

lrs = Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To lrs

Sheets("Table B").Range("E" & i) = "=IF(LOOKUP('Table A'!RC[-4],'Table B'!C1,'Table B'!C1)='Table B'!RC[-4],"""",""S"")"
Sheets("Table B").Range("F" & i) = "=IF(LOOKUP('Table A'!RC[-5],'Table B'!C1,'Table B'!C1)='Table B'!RC[-5],IF('Table B'!RC[-4]='Table A'!RC[-4],"""",""R""),IF('Table B'!RC[-4]='Table A'!RC[-4],"""",""R""))"
Sheets("Table B").Range("E" & i).Value = Sheets("Table B").Range("E" & i).Value
Sheets("Table B").Range("F" & i).Value = Sheets("Table B").Range("F" & i).Value
Next i
End Sub

sithelo
03-15-2012, 11:41 PM
Thanks a lot.
I want to make it automatic with out user clicking start macro. I also dont want the user seeing the second sheet.
Also the what if a user inserts a new row, which is likely.
To clarify things, the S stands for description change, R property change.
We have N for a new product, which is done by inserting a new row.

sithelo
03-15-2012, 11:49 PM
Thanks a lot.
I want to make it automatic with out user clicking start macro. I also dont want the user seeing the second sheet.
Also the what if a user inserts a new row, which is likely.
To clarify things, the S stands for description change, R property change.
We have N for a new product, which is done by inserting a new row.