Copy Cell color interior to another cell without effecting its contents

[thequiff]

Ideally, I want to avoid conditional formatting so that I can apply this. Ideally a custom Formula

So above in C1 it is simply =A2
I would like to have USER formula to get the color interior of A2 and apply it to cell C2
How do I go about this?
I found this to extract the color as a decimal

Function MyBackgroundColor(cell As Range)
MyBackgroundColor = cell.Interior.Color
End Function

but the above function won't work if there is data in the cell it's getting the color from. But then I don't know how to use that color to apply to another cell.

 

[mumps]

Try:

Sub CopyColor()
    Application.ScreenUpdating = False
    Dim rng As Range
    For Each rng In Range("A2", Range("A" & Rows.Count).End(xlUp))
        rng.Offset(, 2).Interior.ColorIndex = rng.Interior.ColorIndex
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[thequiff]

Try:

Sub CopyColor()
    Application.ScreenUpdating = False
    Dim rng As Range
    For Each rng In Range("A2", Range("A" & Rows.Count).End(xlUp))
        rng.Offset(, 2).Interior.ColorIndex = rng.Interior.ColorIndex
    Next rng
    Application.ScreenUpdating = True
End Sub

Many Thanks. however is it possible to turn that into a user function?
When by in the formula you specific which cell to color?

=CopyIntColor("from" (A1) "to" (G9)

Something along the lines of that?

 

[mumps]

I'm not sure but I don't think that a function can format a cell with color. Have you looked at conditional formatting?

 

[thequiff]

I'm not sure but I don't think that a function can format a cell with color. Have you looked at conditional formatting?

I'm trying to do this route and avoid conditional formatting as I need to apply it to an existing sheet.

 

[thequiff]

Does anyone know if that can be turned into a user function please?

 

[bebo021999]

Right click on tab' name, then click ViewCode then paste below code into. This trigger any change in column C, from C2 to C7.
Now in C2, input:
=A2
Then drag down

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.Volatile
Dim cell As Range
If Intersect(Target, Range("C2:C7")) Is Nothing Then Exit Sub
For Each cell In Target
    If cell.Value = cell.Offset(, -2).Value Then cell.Interior.Color = cell.Offset(, -2).Interior.Color
Next
End Sub

 

[thequiff]

Right click on tab' name, then click ViewCode then paste below code into. This trigger any change in column C, from C2 to C7.
Now in C2, input:
=A2
Then drag down

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.Volatile
Dim cell As Range
If Intersect(Target, Range("C2:C7")) Is Nothing Then Exit Sub
For Each cell In Target
    If cell.Value = cell.Offset(, -2).Value Then cell.Interior.Color = cell.Offset(, -2).Interior.Color
Next
End Sub

Thats great thanks, obviously this leads to another question now I need to apply the code.

If I do the same but on Sheet 2 but needs to reference the cell it came from on sheet 1?
This is the formula in one of the cells
=IF(OFFSET(SKUs!B2,'1'!J2,0)=0,"",(OFFSET(SKUs!B2,'1'!J2,0)))

So would need to get the colour from sheet "SKU's" on B2


Link to barcodes Xls (We transfer)