Forum Discussion
Formatting based on searching for identical cell in another sheet
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!
9 Replies
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 SubMaybe with this code. In the attached file you can click the button in cell G2 to start the macro.
- 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!
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.
- 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.
- 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.
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.
- Thank you, XLOOKUP works for the comments! Colour coded manually on the first sheet.
