Value with a background colour - automatically change background colour of same value in other sheet

[citullipan]

Hello,
I constantly work with files which always have at least two sheets, and these sheets happen to contain cells with the same values.
I deal with several dossiers, which shift from being "open" to being "closed". When a dossier is open, the colour is yellow and when it's closed and ok, it's green.
The two sheets I work on are similar but not exactly the same, i.e. on one sheet I might happen to have 20 dossiers and on the other one I might happen to have 30.
My question is, how do I make the same value change colour in sheet 2 when I change it in sheet 1?
The value in sheet 2 is, most of the time, on a different row compared to the position it has in sheet 1, so really it's all about changing the colour of the VALUE and not of the position
Example of how I would like it to be:

Thank-you

 

[Gerald Higgins]

Are you colouring them in through Conditional Formating ? If so, just apply the same CF to both sheets.

 

[citullipan]

No, I am colouring them manually because every single dossier has a specific ID, so I can't make a "rule". I just see the ID, copy it in my portal and check its status. If it's still ongoing i turn it yellow, if it's closed I turn it green. I would like that same ID (value) to change colour also in Sheet 2, because otherwise when I will go and work onto Sheet 2 I won't realize which dossiers I have already checked and I will do a "double work"

 

[Gerald Higgins]

So when you close a dossier, do you just turn the cell green to mark it as closed ?
Or do you input the word "closed" (or similar) into a cell somewhere.
If it's the latter, then I suggest you use Conditional Formating, to pick up the input value of "closed" and mark the relevant cells green.
Even if you're not doing this now, i.e. entering the word "closed", I would recommend that you consider doing it.
If it's the former, then you'll need a VBA solution I think, and personally I'm not a VBA expert, although many others on this board are.

 

[citullipan]

OK, that is a good advice.
So, if next to the ID column I add another column where I will put the word "closed", is there a way to make it appear also on sheet 2, in corrispondence of the same value?

 

[Gerald Higgins]

Yes, if you use a formula like VLOOKUP or INDEX/MATCH on your second sheet, to lookup the Closed / Open status from the first sheet, based on the reference for the dossier (its name or ID number etc)

 

[citullipan]

Ok, that is very useful.
Nonetheless, since I also use other colors (for example, red for critical dossiers) I would also like to know if there is a way do automatically change the colour, just like I asked in the first place. SO I hope someone expert in VBA will answer
This is because my mental organization works better with colours.
Otherwise, your solution could still work. thankyou very much