CODEWITHSUNDEEP
vbabackground-colorfillmirror
Zim
Zim

Reputation: 3

Mirroring Sheet1 to Sheet2 for Interior Color only, through VBA

I have a schedule showing a lot of information. I would like to condense this onto a second sheet that displays the fill color only and none of the values.

I want that any fill color changes are automatically copied from sheet1 to sheet2.

I want the code to work with a specific cell range as they differ from both sheets, (Sheet1 is "D8:QP27) & (Sheet2 is B3:QN22) and to get it to mirror at all.

Sheet1 showing all information
enter image description here

Sheet2 showing fill (Interior.Color)
enter image description here

Upvotes: 0

Views: 77

Answers (1)

FunThomas
FunThomas

Reputation: 29296

It looks as if you also want to copy the borders (eg the diagonal border of column I), so I would suggest you use PasteSpecial with the option xlPasteFormats. See https://learn.microsoft.com/en-us/office/vba/api/excel.range.pastespecial

With ThisWorkbook
    .Worksheets("Sheet1").Range("D8:QP27").Copy
    .Worksheets("Sheet2").Range("B3:QN22").PasteSpecial xlPasteFormats
End With

Update: As you are looking for a trigger to copy the format automatically. First step is to create a subroutine:

Sub copyFormat()
    Application.ScreenUpdating = False
    With ThisWorkbook
        .Worksheets("Sheet1").Range("D8:QP27").Copy
        .Worksheets("Sheet2").Range("B3:QN22").PasteSpecial xlPasteFormats
        Application.CutCopyMode = False
    End With
    Application.ScreenUpdating = True
End Sub

Now you need to find a way to call the code, eg

o call the routine from the Worksheet_SelectionChange-event (drawback: as this is rather slow, it could annoy the user)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    copyFormat
End Sub

o Place a button on the sheet to call the routine.

o Use a Timer to call the routine every n seconds (see https://learn.microsoft.com/en-us/office/vba/api/excel.application.ontime). Plenty of examples on SO and elsewhere

Upvotes: 0

Related Questions