If cell colour is, then change cell colour in another tab (with matching value).

Roksman

New Member
Joined
Nov 2, 2016
Messages
4
Hi All,

I confess that I initially tried this on Google Sheets and posted in another thread, but I am going to now recreate this work in Microsoft Excel as it just seems the better option given it appears VBA is the better option - so please ignore the screenshots being GS.

I.e. If I change A9 cell colour to red, can I then have the same value cell in tab "Midfielders" (A15) also change to red automatically?

Effectively, tab "2022 list" is my primary checklist and as I highlight the cells in this list, can the corresponding matching name across tabs 'DEFENDERS', 'MIDFIELDERS', 'RUCKS' and "FORWARDS' mimic the cell colour as I highlight them?

Any assistance will be much appreciated. Thanks.

1644531946839.png
1644531960207.png
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
There is no event to detect (that triggers with) a change of color in a cell so try this workaround. Note that the cells in the other sheets will color only when you move, in the same sheet, from the cell in column A after changing it's color, elsewise it won't trigger (like changing sheet).
As per your example, in a test book in ThisWorkBook's module paste this macro:
VBA Code:
Option Explicit
Private Sub Workbook_Open()
    Range("B1").Select                            '<- as range choose an unused cell but not in column A
End Sub
and in the sheet '2022 List''s module paste this:
Code:
Option Explicit
Public LastRange As Range
Public LastCellColor As Integer
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim myArr  As Variant
    Dim sht    As Variant
    Dim fndRow As Range
    If Target.Cells.CountLarge > 1 Then Exit Sub
    If LastCellColor = 0 Then GoTo setvar
    If LastRange.Interior.ColorIndex <> LastCellColor Then
        If Not Intersect(LastRange, Range("A:A")) Is Nothing Then
            myArr = Array("DEFENDERS", "MIDFIELDERS", "RUCKS", "FORWARDS") 'list of sheets
            For Each sht In myArr
                With Sheets(sht)
                    Set fndRow = .Range("A:A").Find(What:=LastRange.Value, LookIn:=xlValues, LookAt:=xlWhole) 'search for name
                    If Not fndRow Is Nothing Then
                        .Range("A" & fndRow.Row).Interior.ColorIndex = LastRange.Interior.ColorIndex 'color cell found
                    End If
                End With
            Next sht
        End If
    End If
setvar:
    Set LastRange = Target
    LastCellColor = Target.Interior.ColorIndex
End Sub
If the test goes well, try integrating everything into your project.
 
Upvote 0
Solution
Glad having been of some help(y).
The marco has no apparent issue with your test example but since it has been freshly coded has never been thoroughly tested. I would say that you will be the second beta-tester (I'm the first) or first on the field :giggle:. A professional would probably make it better :biggrin:.
 
Upvote 0

Forum statistics

Threads
1,223,365
Messages
6,171,654
Members
452,415
Latest member
mansoorali

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top