Find cells whose value have changed

Question

I have a macro which should be triggered when any cell in the range (s1:s100) changes. Range s1:s100 is populated by another macro and the values keep changing

I have the below piece of code, which works fine when only one cell in the range is changed. If more than one cell changes at the same time, it doesn't work. When more than one cell changes at a time, I get the message "No Change".

Sub Worksheet_Change(ByVal Target As Range)
       Dim keyscells as Range
       Set KeyCells = Range("S1:S100")
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
               Is Nothing Then
      msgbox Target.Address
    'execute my macro
    else
       msgbox 'no change'
    end if
end sub

Any help appreciated!

Added more info When I edit more than one cell at the same time, say S1 S4 and S8, It will say S1 changed (Just pick the first one). But I need to identify all the three cells that was changed.

Thanks,

Valli

Do you have Option Explicit set? `DIm keys cells as Range` isn't going to work for you. — DeanOC, Jun 25 '18 at 07:35
This is a duplicate of [VBA code doesn't run when cell is changed by a formula](https://stackoverflow.com/q/11406628/845655) — DeanOC, Jun 26 '18 at 22:08

score 0 · Answer 1 · answered Jun 25 '18 at 20:38

0

I've copied your modified sub to an Excel 2013 spreadsheet, and I can't see the problem.

If I enter some numbers at the start of col S, then select 3 non-contiguous cells and press delete, then the Target address is the addresses of the 3 cells. Here's a screenshot.

Please provide steps on how someone can replicate your problem.

answered Jun 25 '18 at 20:38

DeanOC

7,142
6
42
56

You must have fixed a typo - OP has **Keyscells** and **Keycells**. – urdearboy Jun 26 '18 at 00:23
1

I was able to replicate issue when typo exists – urdearboy Jun 26 '18 at 00:23
@urdearboy, yes, I had actually fixed the typo in my spreadsheet when replying to an earlier version of OP's question, which also had a typo. I didn't notice that the amended version still had a typo :) – DeanOC Jun 26 '18 at 02:07
Thanks for your replies.. S1:s100 is driven by formulas. For ex, s5, s10 and s15 has the formula "=T1". When I change the value of T1, the macro runs only for S5 and not for S10 & S15 – Valli Jun 26 '18 at 18:06

Find cells whose value have changed

1 Answers1