Copy the cell color from SheetA to SheetB that has the same cell value

user_15774444 0
Viewed 25

This is my first time posting a question here so please bare with me as I try to explain my problem the best I can.

I have two sheets in my workbook where Sheet1 visually represents a position of multiple units (A1 to A162) in a tray with 162 squares. Not all of these squares are filled up as some are empty.

Now sheet 2 shows a numerical value of units A1 to A162. I have used conditional formatting to assign colors for each value.

I was trying to copy the color of A1 from sheet2 to the cell with A1 value from sheet1 but to no success.

Have attached here the links of the 2 sheets. I'm sure the excel wizards here will find this an easy problem and hopefully you guys could help me with my problem. Hoping to hear from the experts here soon.

Sheet1:
enter image description here

Sheet2:
enter image description here

formula excel
1 Answers

I hope I understood the question correct. I had to read it couple of times to get it. Below you can see a VBA solution that worked for me.

Sub color_cells()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim cl As Range, rng As Range
Dim str_a As String

Set ws1 = ThisWorkbook.Sheets("Sheet1")
Set ws2 = ThisWorkbook.Sheets("Sheet2")

For Each cl In ws2.Range("A1:EZ30")
    
    If Not IsError(cl.Value) And cl.Value Like "A*" Then

        str_a = cl.Value
        
        With ws1
            Set rng = .Range("B:B").Find(str_a)
            Set rng = rng.Offset(0, 2)
        End With
        
        cl.Interior.ColorIndex = rng.Interior.ColorIndex
        
    End If
Next

End Sub
user_104526450
Related