Change cells colour automatically which has same datas

[geethgeethan]

For example……
( Please explain and give a solution step by step)



Student ids (sheet 1-it has more column like this)
Column 1. Column 2. Column 3
0000 Sunil. Maths
1111. Gayan. Science
2222. Ranjith. History
3333. Tharsh. ICT

Sheet 2 ( IDs only )
0000
1111
2222
3333


- I add red colour to 0000 cell manually.
When i add this , it has to change 0000 cell colour as red on sheet 2 automatically.
- And also If i change 0000 cell’s colour on sheet 1, it has to copy 0000 cell’s colour on sheet 2 automatically.



Please explain and give a solution step by step:

 

[Max1616]

I would use some VBA that triggers once the sheet 1 is deactivated. To do this, add the below code to the Sheet module/object in the VBA interface for sheet 1:

Private Sub Worksheet_Deactivate()

Dim i As Long
Dim j As Long
Dim lrow1 As Long
Dim lrow2 As Long
Dim ws As Worksheet
Dim TargetID As String
Dim TargetIDColor

Set ws = Sheets(2) 'You may need to rename this if the ID worksheet is not the second worksheet in your workbook

lrow1 = Cells(Rows.Count, 1).End(xlUp).Row 'Last row of your active sheet
lrow2 = ws.Cells(Rows.Count, 1).End(xlUp).Row 'Last row of the id sheet

For i = 2 To lrow1 'Loop through all ids (column A) in active sheet

    If Cells(i, 1).Interior.Color <> "16777215" Then 'If the color of the cell is not blank (code for blank cell color) then fire code below
      
        TargetID = Cells(i, 1) 'Store the student id
        TargetIDColor = Cells(i, 1).Interior.Color 'Store the cell color
      
        For j = 2 To lrow2 'loop through all ids (column A) of the ID sheet
            If ws.Cells(j, 1) = TargetID Then ws.Cells(j, 1).Interior.Color = TargetIDColor 'If the ID in this column matches the TargetID, then set the cell color to whatever it is on the original sheet
        Next j
      
    End If

Next i

End Sub

I tried commenting the code to walk you through the thought process. Let me know if you have questions.

 

[geethgeethan]

I would use some VBA that triggers once the sheet 1 is deactivated. To do this, add the below code to the Sheet module/object in the VBA interface for sheet 1:

Private Sub Worksheet_Deactivate()

Dim i As Long
Dim j As Long
Dim lrow1 As Long
Dim lrow2 As Long
Dim ws As Worksheet
Dim TargetID As String
Dim TargetIDColor

Set ws = Sheets(2) 'You may need to rename this if the ID worksheet is not the second worksheet in your workbook

lrow1 = Cells(Rows.Count, 1).End(xlUp).Row 'Last row of your active sheet
lrow2 = ws.Cells(Rows.Count, 1).End(xlUp).Row 'Last row of the id sheet

For i = 2 To lrow1 'Loop through all ids (column A) in active sheet

    If Cells(i, 1).Interior.Color <> "16777215" Then 'If the color of the cell is not blank (code for blank cell color) then fire code below
     
        TargetID = Cells(i, 1) 'Store the student id
        TargetIDColor = Cells(i, 1).Interior.Color 'Store the cell color
     
        For j = 2 To lrow2 'loop through all ids (column A) of the ID sheet
            If ws.Cells(j, 1) = TargetID Then ws.Cells(j, 1).Interior.Color = TargetIDColor 'If the ID in this column matches the TargetID, then set the cell color to whatever it is on the original sheet
        Next j
     
    End If

Next i

End Sub

I tried commenting the code to walk you through the thought process. Let me know if you have questions.

Thanks for the response. I’ll contact you if I want anything….