VBA - Workbook_SheetChange

[Janko32]

Hello,

I am very new to VBA so this might be a dumb question.

For our company, we have an Excel workbook that has multiple sheets.

We have a Master sheet that all the changes will be made to.

The other sheets are Branches of the master that have linked cells to the master.

I know that there is a way to implement a worksheet_change in order to change the interior color of a cell when it is MANUALLY changed and I know how to implement that.

However, is there a way to implement that same color change across all NON-MANUALLY changed cells within the workbook?

 

[6StringJazzer]

What do you mean by "NON-MANUALLY changed cells"?

There is no event that will tell you if the result of a formula changed, if that's what you are getting at. You can tell when a recalculation occurs, but there is no easy way to find out if a cell with a formula changed its result.

There might be some way of doing this but it would be a bit involved, like tracing every changed value through the formulas it affects.

 

[Janko32]

There are no formulas. Let me try to give you an example so you can better understand what I'm trying to do.

Lets say cell A1 in sheet 1 has the word 'Hello'.

I want cell B2 in sheet 2 to have whatever cell A1 in sheet 1 has... therefore cell B1 in sheet 2 would also have the word 'Hello'.

Now, lets say I want to change cell A1 in sheet 1 to 'Goodbye', I would want cell A1 in sheet 1 to highlight in a light green color. I would then want cell B2 in sheet 2 to say 'Goodbye' (Which it will already do since it is linked to cell A1 in sheet 1) but more importantly, highlight cell b1 in that same light green color.

Does this make more sense?

Sorry to be confusing.

 

[Janko32]

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Interior.Color = RGB(216, 228, 188)
End Sub

This is what I used to change the color of a cell if it was changed on the master sheet. Which it does work, but just for that sheet.

I just don't know how to tell vba, "Hey, I want you to fill the background of all cells that were changed across the workbook."

 

[6StringJazzer]

There are no formulas.

OK

Now, lets say I want to change cell A1 in sheet 1 to 'Goodbye', I would want cell A1 in sheet 1 to highlight in a light green color. I would then want cell B2 in sheet 2 to say 'Goodbye' (Which it will already do since it is linked to cell A1 in sheet 1)

If B2 does not have a formula, how is it linked to A1?

=Sheet1!A1

is a formula. And Excel has no event that can tell you that the result changed.

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: VBA-WorkBook_Change
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Janko32]

OK

If B2 does not have a formula, how is it linked to A1?

=Sheet1!A1

is a formula. And Excel has no event that can tell you that the result changed.

Sorry for the misunderstanding.

If there is no event to handle that, is there a way I can store the worksheet to a variable before the changes were made and then store the changed worksheet to a new variable after the changes has been made.

Then I guess its just looking at the changes and highlighting the differences.

I just don't know VBA well enough to do this.