Reference a cell to copy data + cell shading – is this possible?

[Canadian_Newb]

This is what I would like to have done:

On Sheet1 I want to reference a cell in Sheet2 (simple =Sheet2!B2 for example). Aside from the data that is in the referenced cell, I would like to copy the cell shading itself. I am not entirely sure if that’s possible and if so, how might this be accomplished? Is there a combination of formulas that can do this or would it be a VBA script?

Any guidance would be much appreciated

Thanks!

 

[JayMess]

This will work if Cell C1 on Sheet 1 is the cell you want to manipulate based on what is in cell B2 on Sheet 2.

Sub test123()
'
Dim fill_pattern As String
Dim fill_color As String
    'Cell C1 on Sheet1 is the target to be populated with value and fill from cell B2 on Sheet2
    Sheets("Sheet1").Select
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "=Sheet2!R[1]C[-1]"
    Sheets("Sheet2").Select
    Range("B2").Select
    
    With Selection.Interior
    
        fill_pattern = .Pattern
        fill_color = .Color
        
    End With
    
    Sheets("Sheet1").Select
    Range("C1").Select
    
    With Selection.Interior
    
        .Pattern = fill_pattern
        .Color = fill_color
        
    End With
        
End Sub

The pattern (default is usually solid) and color (default is usually yellow) will be transferred to your target of C1 on Sheet 1.