copy cell color to another sheetint cell with same value

Hi,

i have 2 sheets
in sheet1 there is a row with numbers in one column
in sheet2 there are the same numbers, but in random positions across the sheet

what i want to do: if i change the background color of any number on sheet1 , it should also change the color of the same number in sheet2.

After trying to find out how this could be made possible, i found out the only way this could be done is by vba.
But since i have 0 experience in vba, I would appreciate it, if someone could help me out here.

I attached an example file of my current file structure
i hope it helps.

thanks maniacb
sorry for the long wait.
if i try your solution nothing happens.
But even if i download your file to test it there it doesnt work.
Am I missing something?

hi, zerberus123 maniacb's code works very well for me but i thing what is your mistake you do not double click inside the cell do it and see the result

ah thx
works for me as well now.
is there any way to avoid double clicking the cells?
the whole point is that i dont change the numbers, only the colors.
if i change the color of lets say 50 numbers i dont wanna click each of them

you right it takes from you more time actually I'm like you I always ask help from experts in this forum i have no experience to write code i suggest waiting for maniacb surly it will be back and amending the code I suggest when you clear the whole color in sheet1 then clear all colored values in sheet 2 to be come the code is complete
best, regards
abdelfattah

Sadly if i implement my own File into the test sheet i still doesnt work (even if i double click).
I attached a more correct sample file so it's clearer how my files look like.

that's because the column a="" and the code begins from column a and as for to data begins column d so change

from this

HTML Code: 

to this

HTML Code: 

nice
now i just need maniacb to help me avoiding to have to click on every one of them.
if he finds a way to include background color by conditional format as well i got everything i need

Give this code a try. After changing the color you have to select a different cell and the colors will update. Thanks @Adelfatta for the assist.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim clr, lr, lc, i, j As Long
Dim ws As Worksheet
Dim rec As Range

Set ws = Sheet2
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A:A")) Is Nothing Then
    lr = ws.Range("D" & Rows.Count).End(xlUp).Row
    lc = ws.UsedRange.SpecialCells(xlCellTypeLastCell).Column
    clr = Target.Interior.Color
    For i = 1 To lr
        For j = 1 To lc
            If Target.Value = ws.Cells(i, j).Value Then
                ws.Cells(i, j).Interior.Color = clr
            End If
        Next j
    Next i
End If
End Sub

hmm
on my end it still triggers only when i double click on the cell

it will trigger with a double click, but you only have to do one double click, not one for each color.

thats the thing
it only triggers for the number i double click on
applied your code on my old sample

You're right. Can your process support using the ENTER button to force the change? In other words, can you place the cursor on A1 and hold down the enter button until all cells are cycled through. Sorry, I have to think on this some more to give you the desired solution. Would a single update button better meet your needs?

Here is a triggered solution

Sub transcolor()

Dim clr, lr, lc, i, j, lr1, u As Long
Dim ws, ws1 As Worksheet
Dim rec As Range
Set ws1 = Sheet1
Set ws = Sheet2
    lr = ws.Range("a" & Rows.Count).End(xlUp).Row
    lc = ws.UsedRange.SpecialCells(xlCellTypeLastCell).Column
    lr1 = ws1.Range("a" & Rows.Count).End(xlUp).Row
For u = 1 To lr1
    For i = 1 To lr
        For j = 1 To lc
            If ws1.Cells(u, 1).Value = ws.Cells(i, j).Value Then
                ws.Cells(i, j).Interior.Color = ws1.Cells(u, 1).Interior.Color
            End If
        Next j
    Next i
Next u

End Sub

Hi,
no cycling through doesn work. I need to double click it ie. i have to edit it for it to change the color on the other sheet.

what do i have to do with the triggered solution? Right now i got no change with it, even with double click.

Sure an update button would be fine as well, if it can save you some work

For the trigger/Button solution, enter a button and assign the attached code. I did it in the attached file for you to see.

i have same problem right here,
but in my case,
i have bunch of number with colorful background cell in sheet "Master"
from second sheet called "Edit" i have tons a numbers without background
how should i do if i wanna use that VBA Code?

P.S : its only one coloumn on each worksheet

Welcome to the Forum. Start a new thread and make sure you provide example data as per the instructions in the yellow section above.