Formatting based on searching for identical cell in another sheet

[AlexMillar]

Hi,

Does anyone know if it is possible to do this? I have two spreadsheets containing data from two consecutive months. In the earlier spreadsheet, let's say I have a list of customers colour coded by type, with some information about them in a few cells along the row, and then a column for comments at the end. In the second spreadsheet I have a new list of customers with some information on each, which will contain many of the same customers, but in a different order, not colour coded and without any comments. I want to update the second spreadsheet so that all customers which are also in the first spreadsheet get colour coded the same way as they are in that, and I want the relevant comment to appear in the comments column for that customer in the new spreadsheet. Then I only need to update the new customers rather than going through the old customers manually.

So I need every cell in column B of the new spreadsheet to search through column B of the old spreadsheet looking for an identical match. If it finds it, I need it to fill the cell with the same colour as that cell in the old spreadsheet, and then I need it to add the comment from column M in the old spreadsheet to column M of the new spreadsheet in the same row as the cell it is colour coding. 

Thanks a lot if anyone is able to help!

[#2]

@AlexMillar 

Are the customers on the first sheet color-coded manually or using conditional formatting?

To copy the comment, you can use an IFERROR/VLOOKUP combination, or XLOOKUP if you have Microsoft 365 or Office 2021.

[#3]

@AlexMillar 

I suspect it is indeed possible, probably fairly easy. HOWEVER, it's also likely that a wholly different approach might even be better. 

Is it possible for you to post a copy--just without real names and other identifiers--of your two spreadsheets? Use OneDrive or GoogleDrive if you aren't able to post them here in this forum. Then post a link to them.

[#4]

@AlexMillar 

Sub color()
Dim i As Integer
Dim j As Integer
Dim company As Range

Set company = Tabelle1.Range("B2:B10000")

For i = 2 To 26

j = Application.WorksheetFunction.Match(Tabelle2.Cells(i, 2), company, 0)

Tabelle2.Cells(i, 13).Interior.ColorIndex = Tabelle1.Cells(j + 1, 2).Interior.ColorIndex
Tabelle2.Cells(i, 13).Value = Tabelle1.Cells(j + 1, 13).Value

Next i

End Sub

Maybe with this code. In the attached file you can click the button in cell G2 to start the macro.

[#5]

Thank you, XLOOKUP works for the comments! Colour coded manually on the first sheet.

[#6]

Thanks for the response! XLOOKUP works great for the comments so I'll just wait a bit to check there isn't an equally easy solution incoming for the colour coding before I look at altering the sheets and posting them. If there's something easy like the XLOOKUP for comments then that'll be a fantastic solution for me.

[#7]

Thank you! I suspect this is the perfect solution in the hands of a more competent user but unfortunately I'm a bit too clueless to make the necessary adjustments and take that over to my spreadsheets without some learning so I'll look into it a bit more if I don't find a way that is within my circle of competence!

[#8]

The color/colour coding would be more appropriately handled by Conditional Formatting rather than manual setting. That way it's rules-based, and the rules themselves would be what enabled the ongoing application of the same colors. That's how you take advantage of Excel for what Excel itself brings to the party. Otherwise you're basically using Excel for its resemblance to paper ledger sheets, and using it manually, making minimal use of the tool itself. Even the act of breaking what could be a single data base into separate sheets for each month--that in itself is treating this amazing computer-based tool more as a set of paper ledger sheets. So I would really encourage you to consider posting copies (anonymized so as not to reveal real names) of the spreadsheets so we could give suggestions that would involve making more full use of Excel.

[#9]

@mathetes All very good points but sadly in this case the colour coding is done based on qualitative analysis of things not contained on the spreadsheet so I don't think conditional formatting could be used. I'm not sure how easy it'll be to anonymise the spreadsheet and post it but I'll have a look at doing it later. Thanks!

[#10]

I have a solution thanks to your advice. I completely agree that having one database would be much nicer but the monthly data comes from an external source and then needs to be filtered and sorted before I can do anything with it so this monthly update probably can't be helped (although I should probably at least merge them into different tabs in the same file rather than keep the files separate).

That being said, I have now set it up so that rather than change the fill colour of the cells, I have added a column which I assign a number to depending on what I want the colour to be. I then set up conditional formatting to change the fill colour of the relevant cells depending on what the number is. This means that I can use XLOOKUP to get both the number and the comment related to each entry from the previous month that appears again and I can copy the conditional formatting rules so that the colour coding is automatically copied from the previous month.

This will save me a load of time each month, thank you so much for your generosity with your time in helping out!