Excel VBA Code won't run "behind" multiple workbook sheets: Better approach?

Question

I am a complete novice, this is my first VBA code (necessity is mother of . . . inept coding by novice).

Problem: Why is my code not updating in real-time? Or in any time at all? Can it be fixed? Do I need to somehow put all 16 sheets worth of VBA code into a "module" or do some other trick to fix it?

Background:

I have VBA code "behind" multiple "client" spreadsheets in a workbook. The code allows cell colors to transfer to a master "all clients" spreadsheet. The reason I needed the VBA code was that there was a function (and INDEX function) already in the color-filled cells.

The code was not working properly, so I figured out that the references were wrong and edited one of the sheets' VBA code to ensure I had the references right. They were correct. But even getting those edited references in that one sheet's code to work correctly took a bunch of clicking around and saving and reopening the document.

I then needed to fix the code in all the other sheets, starting with one of them. I can't for the life of me get anything to happen even though I made the correct edit. I should have seen colors change, but nothing happened.

Google search led me to the news that just putting code "behind" spreadsheets often doesn't work. One reference said I should place it in a module. But I have no idea how to do that across all of my 16 client sheets.

I'm also working over Remote Desktop which is probably not helping. I could probably send myself the workbook if needed.

Below is my code (one sheet's worth). The references are different across sheets so that the various client's data (in vertical columns) populates on the correct horizontal rows of the master sheet. Along with that data are the colors that this VBA code is supposed to help render onto the master sheet.

This is the "Glen" spreadsheet's VBA code, Glen's data that needs to be color coded identically on the "WeeklyRatingsAllClients" sheet (ending up in the BD6:CH6 range and BD7:CH7 range) is in the Q4:Q38 range and the U4:U38 range. The other sheets are the exact same except that in the next person's sheet the BD6:CH6 range and BD7:CH7 ranges will update to become BD8:CH8 range and BD9:CH9 and so on sequentially (next client is 10, 11; next is 12, 13 etc.).

If it matters to anyone, I got the original code here and modified it for my needs: https://www.extendoffice.com/documents/excel/4071-excel-link-cell-color-to-another-cell.html

Also, I make a long comment on above page under "Sara" dated 3 months ago that describes more about the code/purpose and shows how I modified the example code for my purpose and it worked--it's just not working now (probably not useful if you already know this stuff well, like I don't).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim xRg As Range
Dim xCRg As Range
Dim xStrAddress As String
xStrAddress = "WeeklyRatingsAllClients!$BD$6:$CH$6"
Set xRg = Application.Range(xStrAddress)
Set xCRg = Me.Range("$Q$4:$Q$38")
On Error Resume Next
For xFNum = 1 To xRg.Count
xRg.Item(xFNum).Interior.Color = xCRg.Item(xFNum).Interior.Color
Next
xStrAddress = "WeeklyRatingsAllClients!$BD$7:$CH$7"
Set xRg = Application.Range(xStrAddress)
Set xCRg = Me.Range("$U$4:$U$38")
On Error Resume Next
For xFNum = 1 To xRg.Count
xRg.Item(xFNum).Interior.Color = xCRg.Item(xFNum).Interior.Color
Next
End Sub

You might want to consider using a workbook-level event here: the Workbook.SheetSelectionChange event. — BigBen
Thanks, BigBen. So here is your referenced example: ````````` Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _ ByVal Target As Excel.Range) Application.StatusBar = Sh.Name & ":" & Target.Address End Sub ```````````` Might someone show me how to edit it for my case? — SC_TimeSavor

BigBen BigBen · Accepted Answer · 2020-10-08T03:23:30

Perhaps use the Workbook.SheetSelectionChange event, something like the following. Note that this can definitely be refactored.

Make sure to add this code in the ThisWorkbook module.
Change "Bob", "Fred", "Joe" to the sheet names in question (in order), and add more Cases as needed, always increasing the offsetNum by 2 from the previous Case.
There's a mismatch in the number of cells on the main sheet vs the client sheet. U4:U38 would be 35 cells, but BD6:CH6 is only 31... more an FYI.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
   
    Dim offsetNum As Long

    Select Case Sh.Name
        Case "Glen"
            offsetNum = 0
        Case "Bob"
            offsetNum = 2
        Case "Fred"
            offsetNum = 4
        Case "Joe"
            offsetNum = 6
        Case Else
            Exit Sub
    End Select
    
    Dim allClientsSheet As Worksheet
    Set allClientsSheet = Me.Worksheets("WeeklyRatingsAllClients")
    
    Dim mainColorRange As Range
    Set mainColorRange = allClientsSheet.Range("BD6:CH6").offset(offsetNum)

    Dim sourceColorRange As Range
    Set sourceColorRange = Sh.Range("Q4:Q38")
    
    Dim i As Long
    For i = 1 To mainColorRange.Rows(1).Cells.Count
        mainColorRange.Rows(1).Cells(i).Interior.Color = sourceColorRange.Cells(i).Interior.Color
    Next
    
    Set sourceColorRange = Sh.Range("U4:U38")
    For i = 1 To mainColorRange.Rows(2).Cells.Count
        mainColorRange.Rows(2).Cells(i).Interior.Color = sourceColorRange.Cells(i).Interior.Color
    Next
End Sub

Excel VBA Code won't run "behind" multiple workbook sheets: Better approach?

1 Answers