Need assistance transferring background coloring across sheets with a formula

[PhillingFrenzy]

Hello all,

New here so forgive me if this is not in the correct place. Having difficulty finding a solution to something I need for work.

I am trying to copy the background color of particular cells as they update onto another sheet. Here is the scenario:

• All cells data and formatting (green, yellow, orange, blue, and blank backgrounds) are updated on Sheet1 manually
• That data is output onto a cleaned-up Sheet2 along with other data
• The shading is not transferring

This is my current formula which does not contain any shader data:
=IFERROR((VLOOKUP(I22,Sheet2!$G:$T,7,FALSE)),"-")

The goal is that all cells are automatically updated as their color statuses change. Unfortunately these all have variable amounts in them so having a value conditioning format formula doesn't seem to apply here. Is there a formula or VBA that can have this occur? I am unfamiliar with VBA but willing to give it a shot.

Please let me know if more information is needed and thank you in advance for any assistance.

**More information added in the comments below**

[Mumps1]

You cannot change a cell color with a formula. You need VBA. It would be easier to help if you could attach a copy of your file. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data. De-sensitize the data if necessary.

[PhillingFrenzy]

Thank you for the quick response. I have attached a sample workbook that contains similar, desensitized data with the formulas that are on the full workbook.

Here is the goal:

• Resources and Actual tabs are updated manually via data insertion
• Data tab aggregates the essential information and displays it in a table form
• This data would be compiled into a PivotTable and be what the end user sees and can siphon through with a Slicer

What needs to happen:

• Cell background color from Columns G:R on the Actual tab need to transfer or be applied to cells L:W on the data tab as they are updated in real time

Ideally this formatting would also carry over to the PivotTable to indicate the status of the payment at a glance however if this is too complex or there is a simpler method (formatting or IF/THEN based on check boxes) then we may rework the spreadsheet to do so. Thank you again for the assistance.

[Mumps1]

In the "Data" sheet, you have 2 rows for each Resource Name. The Account numbers are the same for Doe, Smith and Risy. They are different for Munner. On the "Actual" sheet, you have only one row for Doe, Smith and Risy. Do you want the cell colors for Doe, Smith and Risy to be pasted into both rows on the "Data" sheet for these names? Since Munner has 2 different accounts, do you want to paste the cell colors based on the account? Please clarify.

[PhillingFrenzy]

Yes, the cell colors would need to be pasted based on the individual accounts instead of as a single user.

[Mumps1]

Try:

Sub CopyCellColors()
    Application.ScreenUpdating = False
    Dim LastRow As Long, srcWS As Worksheet, desWS As Worksheet, account As Range, fnd As Range, sAddr As String
    Set srcWS = Sheets("Actual")
    Set desWS = Sheets("Data")
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each account In srcWS.Range("A2:A" & LastRow)
        Set fnd = desWS.Range("B:B").Find(account, LookIn:=xlValues, lookat:=xlWhole)
        If Not fnd Is Nothing Then
            sAddr = fnd.Address
            Do
                srcWS.Range("G" & account.Row).Resize(, 12).Copy
                desWS.Range("L" & fnd.Row).PasteSpecial xlPasteFormats
                Set fnd = desWS.Range("B:B").FindNext(fnd)
            Loop While fnd.Address <> sAddr
            sAddr = ""
        End If
    Next account
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub