CODEWITHSUNDEEP
excelvba
Sim
Sim

Reputation: 1

mirror fill colour after text match of cells between 2 sheets

I have the following problem which I've spent a lot of time researching with no luck. From the forums on this site, I have come to learn that half of my problem can only be solved by VB. I am not proficient so I will need assistance in tacking together the complete solution.

Sheet 1 column A contains a list of items. These items have conditional formatting applied to them, and their fill colours change according to selections in column B, also on sheet 1.

Sheet 2 also contains a column A with list of items.

Sheet 1 column A contains duplicate items. Sheet 2 column A contains unique values. Therefore, we will have to perform a lookup across a range.

I need on sheet 2, where there is a text match between any items between sheet 1 and sheet 2, for the matching cell/s to return the fill colour of the matched cell in sheet 1 column 1.

My logic:

If Sheet2!$A1 finds a match in Sheet1!$A1, then cell on sheet 2 that matches must change its fill colour to match the reference cell on sheet 1.

I have Kutools but could find no way to make it work. I could also not find a way in Excel with conditional formatting.

The end product of this is to link each item in sheet 2 column A, to a smart object in PowerPoint (cell pasted into text field as linked Excel item).

Whenever the colour of an item on sheet 1 column A changes as a result of conditional formatting isolated there, the colour will change in sheet 2 and will reflect in the corresponding item in PowerPoint.

Any help would be greatly appreciated.

-Sim

Upvotes: 0

Views: 568

Answers (1)

GMalc
GMalc

Reputation: 2628

Below is basic code to mirror the current color in a cell if the cells values match. You can modify it to fit your needs.

Dim ws1 As Worksheet
Set ws1 = ThisWorkbook.Worksheets(1)
Dim ws2 As Worksheet
Set ws2 = ThisWorkbook.Worksheets(2)

    If ws2.Range("A2").Value = ws1.Range("A2").Value Then
        ws2.Range("A2").Interior.Color = ws1.Range("A2").DisplayFormat.Interior.Color
    End If

Upvotes: 0

Related Questions