VBA: Identify changed cell(s) in Table row(s)

[inddon]

Hello There,

I have a table. I want to know if there is a way to identify the cell(s) changed in row(s)

example attached file:
1. First row is changed. Cell First name is changed
2. Second row is changed. Cell Age is changed

I want these changed cells to be marked with colour Blue, and also get the cell names and its respective changed value displayed in a message.


Could you please advice how can this be achieved using VBA?


Thanks for your help

Regards
Don

 

[ThauThème]

Hi Don,

Maybe you could use the Evenment Change of the sheet like :

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Interior.ColorIndex = IIf(Target.Value = "", xlNone, 34)
End Sub

 

[inddon]

Hi Don,

Maybe you could use the Evenment Change of the sheet like :

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Interior.ColorIndex = IIf(Target.Value = "", xlNone, 34)
End Sub

Hello ThauTheme,

Thank you for quick reply.

It works half way. Even when I double-click the cell, the colour changes. Also how can I get which cells are changed in which rows in a msgbox?

Regards
Don

 

[ThauThème]

Hi Don,

No ! if you double-Click and edit nothing the color do not change...

I think it's very stange to have a message indicating the value and address of a cell you just change right now !
You edit "Helo" in A5 and you want a message "A5 contain Hello" ?
I think that users of your table want to kill you ??!...

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Interior.ColorIndex = IIf(Target.Value = "", xlNone, 34)
MsgBox "Cell " & Target.Address(0,0) & " from row " & Target.row & " has been changes with: " & Target.Value & "!"
End Sub

 

[inddon]

Hello ThauTheme,

Thank you for you help. Yes you are right message immediately after changing value is not good.

I want to use this in the following manner. I should have mentioned it in the start, my apology:

1. The row is send to an external database. When a user updates any cell in the excel row, only the updated cells changed value should be send for update to the external database, instead of all the cells in the row.

There will be a button 'Send' when clicked it will loop through the table. For every row it will identify the cells changed in the row and will send only the changed cell for update. It will go to the next row and repeat the same.

When I double click on the cell (without any change) and then leave the cell, the color is changed and the message is displayed. This should not happen, it should happen only when value is changed in the cell. I wonder how can this not be reproduced at your end (I am using excel 2010)

Looking forward to hearing from you.

Regards
Don

 

[NARAYANK991]

Hi ,

The problem description is detailed now , and obviously based on this , the solution is also going to be much more complicated.

1. You need to keep track of which cells have been changed ; since this number is not known in advance , the best way would be to use a helper column to track the cells which have been changed ; when the Send button is pressed , all that the code has to do is look at this helper column and send only those cells which have been identified as changed.

2. Since the code has to keep track of the previous value of cells to identify a change , you need to make use of probably all of the following :

Workbook_Open

Worksheet_SelectionChange

Worksheet_Change

so that when ever a Worksheet_Change event is triggered , the cell contents are checked with their previous value to detect whether there has been a change.

Narayan

 

[inddon]

Hi ,

The problem description is detailed now , and obviously based on this , the solution is also going to be much more complicated.

1. You need to keep track of which cells have been changed ; since this number is not known in advance , the best way would be to use a helper column to track the cells which have been changed ; when the Send button is pressed , all that the code has to do is look at this helper column and send only those cells which have been identified as changed.

2. Since the code has to keep track of the previous value of cells to identify a change , you need to make use of probably all of the following :

Workbook_Open

Worksheet_SelectionChange

Worksheet_Change

so that when ever a Worksheet_Change event is triggered , the cell contents are checked with their previous value to detect whether there has been a change.

Narayan

Thanks Narayan for a clear description in a VBA way.

If this is complicated and will involve more coding then I would rather send it via a recordset and update all the DB columns of that table.

Regards
Don