0

I have an Excel workbook that has multiple sheets.
There is a Master sheet where all the changes will be made.
The other sheets are Branches of the master that have linked cells to the master.

I know how to implement a worksheet_change to change the interior color of a cell when it is manually changed.

Is there a way to implement that same color change across all non-manually changed cells within the workbook?

Let's 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, let's say I change cell A1 in sheet 1 to 'Goodbye'

  • I want cell A1 in sheet 1 to highlight in a light green color.
  • I 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.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Target.Interior.Color = RGB(216, 228, 188)
End Sub

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

I want to fill the background of all cells that were changed across the workbook.

Community
  • 1
  • 1
Janko32
  • 3
  • 2
  • 1
    I think you're looking for something like [this](https://stackoverflow.com/questions/10897958/programmatically-select-other-sheet-precedents-or-dependents-in-excel) – Warcupine Dec 07 '21 at 19:18
  • The event is `Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)`, but it's not useful in your case. Do you want this triggered only in e.g. `Sheet1` and then in all (or a list of) worksheets, the cell next to the same cell should be green and the cell in the next column and next row should be the value?. – VBasic2008 Dec 07 '21 at 19:32
  • @VBasic2008 I want to trigger the highlight when a change is made in the master. I also want the highlight to trigger in the cell that was changed in one of the branch sheets. – Janko32 Dec 07 '21 at 19:34
  • @Warcupine I have no idea how to understand that because I am very new to VBA. If it was Python or any other coding language I would probably be able to understand it more. – Janko32 Dec 07 '21 at 19:36

1 Answers1

-1

Your code will apply to all sheets by adding a loop to it and itterating the sheets with their index nr.

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
        Call WorksheetLoop(Sh, Target)
    End Sub
    Private Sub WorksheetLoop(ByVal Sh As Object, ByVal Target As Range)
        Dim WS_Count As Long, i As Long
        WS_Count = ActiveWorkbook.Worksheets.Count
        For i = 1 To WS_Count ' loop.all sheets but don't update the active one
            Worksheets(i).Range(Target.Address).Interior.Color = RGB(216, 228, 188)
        Next i
    End Sub
ceci
  • 589
  • 4
  • 14
  • Consider the following example: [`Sheet1!A1`, `1`], [`Sheet2!B20`, `=Sheet1!A1+B1`],[`Sheet15!D40`, `=Sheet1!A1*25`]. If a change happens in `Sheet1!A1`, OP wants to color all three cells, but he doesn't know about the two other references or if there are any. This is manually done with `Formulas>Formula Auditing>Trace Dependents`. The link provided by Warcupine covers this in VBA, but it's rather complicated. – VBasic2008 Dec 07 '21 at 23:22