Copy cell color to another sheet

M

matherne

New Member
Joined
Feb 17, 2024
Messages
4
Office Version
  1. 365
Platform
  1. Windows
I am very new to VBA. I have been trying to find the right VBA stuff to use to be able to copy over colors from another sheet. There are 6 different sheets in which I want to change the colors on the first sheet and want the designated cells in the next 5 sheets to change colors. I feel like I am so close but the Ranges and Cells are not registering as a value within the Locals tab. I am doing all of this to just calculate the stats per color per category.

What I have so far:

Sub Copy_Cell_Color_to_Another_Sheet()
Dim OverallSheet As Worksheet
Dim DWeldsSheet As Worksheet
Dim AWeldsSheet As Worksheet
Dim LoosePigtailsSheet As Worksheet
Dim TeeDowncomerSheet As Worksheet
Dim HeadersSheet As Worksheet
Dim OverallRange As RANGE
Dim DWeldsRange As RANGE
Dim AWeldsRange As RANGE
Dim LoosePigtailsRange As RANGE
Dim TeeDowncomerRange As RANGE
Dim HeadersRange As RANGE
Dim OverallCell As RANGE
Dim DWeldsCell As RANGE
Dim AWeldsCell As RANGE
Dim LoosePigtailsCell As RANGE
Dim TeeDowncomerCell As RANGE
Dim HeadersCell As RANGE
Set OverallSheet = ThisWorkbook.Worksheets("Overall")
Set DWeldsSheet = ThisWorkbook.Worksheets("DWelds")
Set AWeldsSheet = ThisWorkbook.Worksheets("AWelds")
Set LoosePigtailsSheet = ThisWorkbook.Worksheets("LoosePigtails")
Set TeeDowncomerSheet = ThisWorkbook.Worksheets("TeeDowncomer")
Set HeadersSheet = ThisWorkbook.Worksheets("Headers")
Set OverallRange = OverallSheet.RANGE("G11:BF42")
Set DWeldsRange = DWeldsSheet.RANGE("G11:O11,R11:X11,AO11:AU11,AX11:BF11,G15:O15,R15:X15,AO15:AU15,AX15:BF15,G20:O20,R20:X20,AO20:AU20,AX20:BF20,G24:O24,R24:X24,AO24:AU24,AX24:BF24,G29:O29,R29:X29,AO29:AU29,AX29:BF29,G33:O33,R33:X33,AO33:AU33,AX33:BF33,G38:O38,R38:X38,AO38:AU38,AX38:BF38,G42:O42,R42:X42,AO42:AU42,AX42:BF42")
Set AWeldsRange = AWeldsSheet.RANGE("G12:O12,R12:X12,AO12:AU12,AX12:BF12,G14:O14,R14:X14,AO14:AU14,AX14:BF14,G21:O21,R21:X21,AO21:AU21,AX21:BF21,AX23:BF23,AO23:AU23,R23:X23,G23:O23,G30:O30,R30:X30,AO30:AU30,AX30:BF30,G32:O32,R32:X32,AO32:AU32,AX32:BF32,G39:O39,R39:X39,AO39:AU39,AX39:BF39,G41:O41,R41:X41,AO41:AU41,AX41:BF41")
Set LoosePigtailsRange = LoosePigtailsSheet.RANGE("P12:Q12,Y12:AN12,AV12:AW12,P14:Q14,Y14:AN14,AV14:AW14,P21:Q21,Y21:AN21,AV21:AW21,P23:Q23,Y23:AN23,AV23:AW23,P30:Q30,Y30:AN30,AV30:AW30,P32:Q32,Y32:AN32,AV32:AW32,P39:Q39,Y39:AN39,AV39:AW39,P41:Q41,Y41:AN41,AV41:AW41")
Set TeeDowncomerRange = TeeDowncomerSheet.RANGE("AF13,AF22,AF31,AF40")
Set HeadersRange = HeadersSheet.RANGE("G13:AE13,AH13:BF13,G22:AE22,AH22:BF22,G31:AE31,AH31:BF31,G40:AE40,AH40:BF40")

For Each OverallCell In OverallRange
Set DWeldsCell = Cells(OverallCell.Row, OverallCell.Column)
DWeldsCell.Interior.COLOR = OverallCell.Interior.COLOR

Next OverallCell

For Each OverallCell In OverallRange
Set AWeldsCell = Cells(OverallCell.Row, OverallCell.Column)
AWeldsCell.Interior.COLOR = OverallCell.Interior.COLOR

Next OverallCell

For Each OverallCell In OverallRange
Set LoosePigtailsCell = Cells(OverallCell.Row, OverallCell.Column)
LoosePigtailsCell.Interior.COLOR = OverallCell.Interior.COLOR

Next OverallCell

For Each OverallCell In OverallRange
Set TeeDowncomerCell = Cells(OverallCell.Row, OverallCell.Column)
TeeDowncomerCell.Interior.COLOR = OverallCell.Interior.COLOR

Next OverallCell

For Each OverallCell In OverallRange
Set HeadersCell = Cells(OverallCell.Row, OverallCell.Column)
HeadersCell.Interior.COLOR = OverallCell.Interior.COLOR

Next OverallCell


End Sub
 

Attachments

  • Screenshot 2024-02-17 085115.png
    Screenshot 2024-02-17 085115.png
    104.4 KB · Views: 13
  • Screenshot 2024-02-17 085238.png
    Screenshot 2024-02-17 085238.png
    90.4 KB · Views: 13

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
It would be easier to help if you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).
 
Upvote 0
mumps said:
It would be easier to help if you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).
Click to expand...
Copy of Outlet Progress Sheet.xlsm

Whenever I make changes to the color of the Overall Sheet, I want the designated cells to change colors on the designated sheets

Set DWeldsRange = DWeldsSheet.RANGE("G11:O11,R11:X11,AO11:AU11,AX11:BF11,G15:O15,R15:X15,AO15:AU15,AX15:BF15,G20:O20,R20:X20,AO20:AU20,AX20:BF20,G24:O24,R24:X24,AO24:AU24,AX24:BF24,G29:O29,R29:X29,AO29:AU29,AX29:BF29,G33:O33,R33:X33,AO33:AU33,AX33:BF33,G38:O38,R38:X38,AO38:AU38,AX38:BF38,G42:O42,R42:X42,AO42:AU42,AX42:BF42")

Set AWeldsRange = AWeldsSheet.RANGE("G12:O12,R12:X12,AO12:AU12,AX12:BF12,G14:O14,R14:X14,AO14:AU14,AX14:BF14,G21:O21,R21:X21,AO21:AU21,AX21:BF21,AX23:BF23,AO23:AU23,R23:X23,G23:O23,G30:O30,R30:X30,AO30:AU30,AX30:BF30,G32:O32,R32:X32,AO32:AU32,AX32:BF32,G39:O39,R39:X39,AO39:AU39,AX39:BF39,G41:O41,R41:X41,AO41:AU41,AX41:BF41")

Set LoosePigtailsRange = LoosePigtailsSheet.RANGE("P12:Q12,Y12:AN12,AV12:AW12,P14:Q14,Y14:AN14,AV14:AW14,P21:Q21,Y21:AN21,AV21:AW21,P23:Q23,Y23:AN23,AV23:AW23,P30:Q30,Y30:AN30,AV30:AW30,P32:Q32,Y32:AN32,AV32:AW32,P39:Q39,Y39:AN39,AV39:AW39,P41:Q41,Y41:AN41,AV41:AW41")

Set TeeDowncomerRange = TeeDowncomerSheet.RANGE("AF13,AF22,AF31,AF40")

Set HeadersRange = HeadersSheet.RANGE("G13:AE13,AH13:BF13,G22:AE22,AH22:BF22,G31:AE31,AH31:BF31,G40:AE40,AH40:BF40")
 
Upvote 0
mumps said:
It would be easier to help if you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).
Click to expand...
For example, this was the original coding I was using when I was just using 3 sheets as a test run. The sheets were labeled: Target, Source, Source2

Sub Copy_Cell_Color_to_Another_Sheet()
Dim sourceSheet As Worksheet
Dim targetSheet As Worksheet
Dim source2Sheet As Worksheet
Dim sourceRange As Range
Dim targetRange As Range
Dim source2Range As Range
Dim sourceCell As Range
Dim targetCell As Range
Dim source2Cell As Range
Set sourceSheet = ThisWorkbook.Worksheets("Source")
Set targetSheet = ThisWorkbook.Worksheets("Target")
Set source2Sheet = ThisWorkbook.Worksheets("Source2")
Set sourceRange = sourceSheet.Range("B16:D18")
Set targetRange = targetSheet.Range("B16:D18")
Set source2Range = source2Sheet.Range("C8:D8,C11:D11")
Set targetRange = targetSheet.Range("C8:D8,C11:D11")

For Each sourceCell In sourceRange
Set targetCell = Cells(sourceCell.Row, sourceCell.Column)
targetCell.Interior.Color = sourceCell.Interior.Color

Next sourceCell

For Each source2Cell In source2Range
Set targetCell = Cells(source2Cell.Row, source2Cell.Column)
targetCell.Interior.Color = source2Cell.Interior.Color

Next source2Cell

End Sub
 
Upvote 0
There may be a shorter way but try:
VBA Code: 
Sub Copy_Cell_Color_to_Another_Sheet()
    Application.ScreenUpdating = False
    Dim srcWS As Worksheet, x As Long, y As Long: y = 13
    Set srcWS = Sheets("OverAll")
    With Sheets("DWelds")
        .RANGE("G11").Resize(, 9).Interior.COLOR = srcWS.RANGE("G11").Resize(, 9).Interior.COLOR
        .RANGE("R11").Resize(, 7).Interior.COLOR = srcWS.RANGE("R11").Resize(, 7).Interior.COLOR
        .RANGE("AO11").Resize(, 7).Interior.COLOR = srcWS.RANGE("AO11").Resize(, 7).Interior.COLOR
        .RANGE("AX11").Resize(, 9).Interior.COLOR = srcWS.RANGE("AX11").Resize(, 9).Interior.COLOR
        .RANGE("G15").Resize(, 9).Interior.COLOR = srcWS.RANGE("G15").Resize(, 9).Interior.COLOR
        .RANGE("R15").Resize(, 7).Interior.COLOR = srcWS.RANGE("R15").Resize(, 7).Interior.COLOR
        .RANGE("AO15").Resize(, 7).Interior.COLOR = srcWS.RANGE("AO15").Resize(, 7).Interior.COLOR
        .RANGE("AX15").Resize(, 9).Interior.COLOR = srcWS.RANGE("AX15").Resize(, 9).Interior.COLOR
        .RANGE("G20").Resize(, 9).Interior.COLOR = srcWS.RANGE("G20").Resize(, 9).Interior.COLOR
        .RANGE("R20").Resize(, 7).Interior.COLOR = srcWS.RANGE("R20").Resize(, 7).Interior.COLOR
        .RANGE("AO20").Resize(, 7).Interior.COLOR = srcWS.RANGE("AO20").Resize(, 7).Interior.COLOR
        .RANGE("AX20").Resize(, 9).Interior.COLOR = srcWS.RANGE("AX20").Resize(, 9).Interior.COLOR
        .RANGE("G24").Resize(, 9).Interior.COLOR = srcWS.RANGE("G24").Resize(, 9).Interior.COLOR
        .RANGE("R24").Resize(, 7).Interior.COLOR = srcWS.RANGE("R24").Resize(, 7).Interior.COLOR
        .RANGE("AO24").Resize(, 7).Interior.COLOR = srcWS.RANGE("AO24").Resize(, 7).Interior.COLOR
        .RANGE("AX24").Resize(, 9).Interior.COLOR = srcWS.RANGE("AX24").Resize(, 9).Interior.COLOR
        .RANGE("G29").Resize(, 9).Interior.COLOR = srcWS.RANGE("G29").Resize(, 9).Interior.COLOR
        .RANGE("R29").Resize(, 7).Interior.COLOR = srcWS.RANGE("R29").Resize(, 7).Interior.COLOR
        .RANGE("AO29").Resize(, 7).Interior.COLOR = srcWS.RANGE("AO29").Resize(, 7).Interior.COLOR
        .RANGE("AX29").Resize(, 9).Interior.COLOR = srcWS.RANGE("AX29").Resize(, 9).Interior.COLOR
        .RANGE("G33").Resize(, 9).Interior.COLOR = srcWS.RANGE("G33").Resize(, 9).Interior.COLOR
        .RANGE("R33").Resize(, 7).Interior.COLOR = srcWS.RANGE("R33").Resize(, 7).Interior.COLOR
        .RANGE("AO33").Resize(, 7).Interior.COLOR = srcWS.RANGE("AO33").Resize(, 7).Interior.COLOR
        .RANGE("AX33").Resize(, 9).Interior.COLOR = srcWS.RANGE("AX33").Resize(, 9).Interior.COLOR
        .RANGE("G38").Resize(, 9).Interior.COLOR = srcWS.RANGE("G38").Resize(, 9).Interior.COLOR
        .RANGE("R38").Resize(, 7).Interior.COLOR = srcWS.RANGE("R38").Resize(, 7).Interior.COLOR
        .RANGE("AO38").Resize(, 7).Interior.COLOR = srcWS.RANGE("AO38").Resize(, 7).Interior.COLOR
        .RANGE("AX38").Resize(, 9).Interior.COLOR = srcWS.RANGE("AX38").Resize(, 9).Interior.COLOR
        .RANGE("G42").Resize(, 9).Interior.COLOR = srcWS.RANGE("G42").Resize(, 9).Interior.COLOR
        .RANGE("R42").Resize(, 7).Interior.COLOR = srcWS.RANGE("R42").Resize(, 7).Interior.COLOR
        .RANGE("AO42").Resize(, 7).Interior.COLOR = srcWS.RANGE("AO42").Resize(, 7).Interior.COLOR
        .RANGE("AX42").Resize(, 9).Interior.COLOR = srcWS.RANGE("AX42").Resize(, 9).Interior.COLOR
    End With
    With Sheets("AWelds")
        .RANGE("G12").Resize(, 9).Interior.COLOR = srcWS.RANGE("G12").Resize(, 9).Interior.COLOR
        .RANGE("R12").Resize(, 7).Interior.COLOR = srcWS.RANGE("R12").Resize(, 7).Interior.COLOR
        .RANGE("AO12").Resize(, 7).Interior.COLOR = srcWS.RANGE("AO12").Resize(, 7).Interior.COLOR
        .RANGE("AX12").Resize(, 9).Interior.COLOR = srcWS.RANGE("AX12").Resize(, 9).Interior.COLOR
        .RANGE("G14").Resize(, 9).Interior.COLOR = srcWS.RANGE("G14").Resize(, 9).Interior.COLOR
        .RANGE("R14").Resize(, 7).Interior.COLOR = srcWS.RANGE("R14").Resize(, 7).Interior.COLOR
        .RANGE("AO14").Resize(, 7).Interior.COLOR = srcWS.RANGE("AO14").Resize(, 7).Interior.COLOR
        .RANGE("AX14").Resize(, 9).Interior.COLOR = srcWS.RANGE("AX14").Resize(, 9).Interior.COLOR
        .RANGE("G21").Resize(, 9).Interior.COLOR = srcWS.RANGE("G21").Resize(, 9).Interior.COLOR
        .RANGE("R21").Resize(, 7).Interior.COLOR = srcWS.RANGE("R21").Resize(, 7).Interior.COLOR
        .RANGE("AO21").Resize(, 7).Interior.COLOR = srcWS.RANGE("AO21").Resize(, 7).Interior.COLOR
        .RANGE("AX21").Resize(, 9).Interior.COLOR = srcWS.RANGE("AX21").Resize(, 9).Interior.COLOR
        .RANGE("G23").Resize(, 9).Interior.COLOR = srcWS.RANGE("G23").Resize(, 9).Interior.COLOR
        .RANGE("R23").Resize(, 7).Interior.COLOR = srcWS.RANGE("R23").Resize(, 7).Interior.COLOR
        .RANGE("AO23").Resize(, 7).Interior.COLOR = srcWS.RANGE("AO23").Resize(, 7).Interior.COLOR
        .RANGE("AX23").Resize(, 9).Interior.COLOR = srcWS.RANGE("AX23").Resize(, 9).Interior.COLOR
        .RANGE("G30").Resize(, 9).Interior.COLOR = srcWS.RANGE("G30").Resize(, 9).Interior.COLOR
        .RANGE("R30").Resize(, 7).Interior.COLOR = srcWS.RANGE("R30").Resize(, 7).Interior.COLOR
        .RANGE("AO30").Resize(, 7).Interior.COLOR = srcWS.RANGE("AO30").Resize(, 7).Interior.COLOR
        .RANGE("AX30").Resize(, 9).Interior.COLOR = srcWS.RANGE("AX30").Resize(, 9).Interior.COLOR
        .RANGE("G32").Resize(, 9).Interior.COLOR = srcWS.RANGE("G32").Resize(, 9).Interior.COLOR
        .RANGE("R32").Resize(, 7).Interior.COLOR = srcWS.RANGE("R32").Resize(, 7).Interior.COLOR
        .RANGE("AO32").Resize(, 7).Interior.COLOR = srcWS.RANGE("AO32").Resize(, 7).Interior.COLOR
        .RANGE("AX32").Resize(, 9).Interior.COLOR = srcWS.RANGE("AX32").Resize(, 9).Interior.COLOR
        .RANGE("G39").Resize(, 9).Interior.COLOR = srcWS.RANGE("G39").Resize(, 9).Interior.COLOR
        .RANGE("R39").Resize(, 7).Interior.COLOR = srcWS.RANGE("R39").Resize(, 7).Interior.COLOR
        .RANGE("AO39").Resize(, 7).Interior.COLOR = srcWS.RANGE("AO39").Resize(, 7).Interior.COLOR
        .RANGE("AX39").Resize(, 9).Interior.COLOR = srcWS.RANGE("AX39").Resize(, 9).Interior.COLOR
        .RANGE("G41").Resize(, 9).Interior.COLOR = srcWS.RANGE("G41").Resize(, 9).Interior.COLOR
        .RANGE("R41").Resize(, 7).Interior.COLOR = srcWS.RANGE("R41").Resize(, 7).Interior.COLOR
        .RANGE("AO41").Resize(, 7).Interior.COLOR = srcWS.RANGE("AO41").Resize(, 7).Interior.COLOR
        .RANGE("AX41").Resize(, 9).Interior.COLOR = srcWS.RANGE("AX41").Resize(, 9).Interior.COLOR
    End With
    With Sheets("LoosePigtails")
        .RANGE("P12").Resize(, 2).Interior.COLOR = srcWS.RANGE("P12").Resize(, 2).Interior.COLOR
        .RANGE("Y12").Resize(, 16).Interior.COLOR = srcWS.RANGE("Y12").Resize(, 16).Interior.COLOR
        .RANGE("AV12").Resize(, 2).Interior.COLOR = srcWS.RANGE("AV12").Resize(, 2).Interior.COLOR
        .RANGE("P14").Resize(, 2).Interior.COLOR = srcWS.RANGE("P14").Resize(, 2).Interior.COLOR
        .RANGE("Y14").Resize(, 16).Interior.COLOR = srcWS.RANGE("Y14").Resize(, 16).Interior.COLOR
        .RANGE("AV14").Resize(, 2).Interior.COLOR = srcWS.RANGE("AV14").Resize(, 2).Interior.COLOR
        .RANGE("P21").Resize(, 2).Interior.COLOR = srcWS.RANGE("P21").Resize(, 2).Interior.COLOR
        .RANGE("Y21").Resize(, 16).Interior.COLOR = srcWS.RANGE("Y21").Resize(, 16).Interior.COLOR
        .RANGE("AV21").Resize(, 2).Interior.COLOR = srcWS.RANGE("AV21").Resize(, 2).Interior.COLOR
        .RANGE("P23").Resize(, 2).Interior.COLOR = srcWS.RANGE("P23").Resize(, 2).Interior.COLOR
        .RANGE("Y23").Resize(, 16).Interior.COLOR = srcWS.RANGE("Y23").Resize(, 16).Interior.COLOR
        .RANGE("AV23").Resize(, 2).Interior.COLOR = srcWS.RANGE("AV23").Resize(, 2).Interior.COLOR
        .RANGE("P30").Resize(, 2).Interior.COLOR = srcWS.RANGE("P30").Resize(, 2).Interior.COLOR
        .RANGE("Y30").Resize(, 16).Interior.COLOR = srcWS.RANGE("Y30").Resize(, 16).Interior.COLOR
        .RANGE("AV30").Resize(, 2).Interior.COLOR = srcWS.RANGE("AV30").Resize(, 2).Interior.COLOR
        .RANGE("P32").Resize(, 2).Interior.COLOR = srcWS.RANGE("P32").Resize(, 2).Interior.COLOR
        .RANGE("Y32").Resize(, 16).Interior.COLOR = srcWS.RANGE("Y32").Resize(, 16).Interior.COLOR
        .RANGE("AV32").Resize(, 2).Interior.COLOR = srcWS.RANGE("AV32").Resize(, 2).Interior.COLOR
        .RANGE("P39").Resize(, 2).Interior.COLOR = srcWS.RANGE("P39").Resize(, 2).Interior.COLOR
        .RANGE("Y39").Resize(, 16).Interior.COLOR = srcWS.RANGE("Y39").Resize(, 16).Interior.COLOR
        .RANGE("AV39").Resize(, 2).Interior.COLOR = srcWS.RANGE("AV39").Resize(, 2).Interior.COLOR
        .RANGE("P41").Resize(, 2).Interior.COLOR = srcWS.RANGE("P41").Resize(, 2).Interior.COLOR
        .RANGE("Y41").Resize(, 16).Interior.COLOR = srcWS.RANGE("Y41").Resize(, 16).Interior.COLOR
        .RANGE("AV41").Resize(, 2).Interior.COLOR = srcWS.RANGE("AV41").Resize(, 2).Interior.COLOR
    End With
    With Sheets("TeeDowncomer")
        For x = 1 To 4
            .RANGE("AF" & y).Interior.COLOR = srcWS.RANGE("AF" & y).Interior.COLOR
            y = y + 9
        Next x
    End With
    y = 13
    With Sheets("Headers")
        .RANGE("G13").Resize(, 25).Interior.COLOR = srcWS.RANGE("G13").Interior.COLOR
        .RANGE("AH13").Resize(, 25).Interior.COLOR = srcWS.RANGE("AH13").Interior.COLOR
        .RANGE("G22").Resize(, 25).Interior.COLOR = srcWS.RANGE("G22").Interior.COLOR
        .RANGE("AH22").Resize(, 25).Interior.COLOR = srcWS.RANGE("AH22").Interior.COLOR
        .RANGE("G31").Resize(, 25).Interior.COLOR = srcWS.RANGE("G31").Interior.COLOR
        .RANGE("AH31").Resize(, 25).Interior.COLOR = srcWS.RANGE("AH31").Interior.COLOR
        .RANGE("G40").Resize(, 25).Interior.COLOR = srcWS.RANGE("G40").Interior.COLOR
        .RANGE("AH40").Resize(, 25).Interior.COLOR = srcWS.RANGE("AH40").Interior.COLOR
    End With
    Application.ScreenUpdating = True
End Sub
 
Upvote 1
Solution
mumps said:
There may be a shorter way but try:
VBA Code: 
Sub Copy_Cell_Color_to_Another_Sheet()
    Application.ScreenUpdating = False
    Dim srcWS As Worksheet, x As Long, y As Long: y = 13
    Set srcWS = Sheets("OverAll")
    With Sheets("DWelds")
        .RANGE("G11").Resize(, 9).Interior.COLOR = srcWS.RANGE("G11").Resize(, 9).Interior.COLOR
        .RANGE("R11").Resize(, 7).Interior.COLOR = srcWS.RANGE("R11").Resize(, 7).Interior.COLOR
        .RANGE("AO11").Resize(, 7).Interior.COLOR = srcWS.RANGE("AO11").Resize(, 7).Interior.COLOR
        .RANGE("AX11").Resize(, 9).Interior.COLOR = srcWS.RANGE("AX11").Resize(, 9).Interior.COLOR
        .RANGE("G15").Resize(, 9).Interior.COLOR = srcWS.RANGE("G15").Resize(, 9).Interior.COLOR
        .RANGE("R15").Resize(, 7).Interior.COLOR = srcWS.RANGE("R15").Resize(, 7).Interior.COLOR
        .RANGE("AO15").Resize(, 7).Interior.COLOR = srcWS.RANGE("AO15").Resize(, 7).Interior.COLOR
        .RANGE("AX15").Resize(, 9).Interior.COLOR = srcWS.RANGE("AX15").Resize(, 9).Interior.COLOR
        .RANGE("G20").Resize(, 9).Interior.COLOR = srcWS.RANGE("G20").Resize(, 9).Interior.COLOR
        .RANGE("R20").Resize(, 7).Interior.COLOR = srcWS.RANGE("R20").Resize(, 7).Interior.COLOR
        .RANGE("AO20").Resize(, 7).Interior.COLOR = srcWS.RANGE("AO20").Resize(, 7).Interior.COLOR
        .RANGE("AX20").Resize(, 9).Interior.COLOR = srcWS.RANGE("AX20").Resize(, 9).Interior.COLOR
        .RANGE("G24").Resize(, 9).Interior.COLOR = srcWS.RANGE("G24").Resize(, 9).Interior.COLOR
        .RANGE("R24").Resize(, 7).Interior.COLOR = srcWS.RANGE("R24").Resize(, 7).Interior.COLOR
        .RANGE("AO24").Resize(, 7).Interior.COLOR = srcWS.RANGE("AO24").Resize(, 7).Interior.COLOR
        .RANGE("AX24").Resize(, 9).Interior.COLOR = srcWS.RANGE("AX24").Resize(, 9).Interior.COLOR
        .RANGE("G29").Resize(, 9).Interior.COLOR = srcWS.RANGE("G29").Resize(, 9).Interior.COLOR
        .RANGE("R29").Resize(, 7).Interior.COLOR = srcWS.RANGE("R29").Resize(, 7).Interior.COLOR
        .RANGE("AO29").Resize(, 7).Interior.COLOR = srcWS.RANGE("AO29").Resize(, 7).Interior.COLOR
        .RANGE("AX29").Resize(, 9).Interior.COLOR = srcWS.RANGE("AX29").Resize(, 9).Interior.COLOR
        .RANGE("G33").Resize(, 9).Interior.COLOR = srcWS.RANGE("G33").Resize(, 9).Interior.COLOR
        .RANGE("R33").Resize(, 7).Interior.COLOR = srcWS.RANGE("R33").Resize(, 7).Interior.COLOR
        .RANGE("AO33").Resize(, 7).Interior.COLOR = srcWS.RANGE("AO33").Resize(, 7).Interior.COLOR
        .RANGE("AX33").Resize(, 9).Interior.COLOR = srcWS.RANGE("AX33").Resize(, 9).Interior.COLOR
        .RANGE("G38").Resize(, 9).Interior.COLOR = srcWS.RANGE("G38").Resize(, 9).Interior.COLOR
        .RANGE("R38").Resize(, 7).Interior.COLOR = srcWS.RANGE("R38").Resize(, 7).Interior.COLOR
        .RANGE("AO38").Resize(, 7).Interior.COLOR = srcWS.RANGE("AO38").Resize(, 7).Interior.COLOR
        .RANGE("AX38").Resize(, 9).Interior.COLOR = srcWS.RANGE("AX38").Resize(, 9).Interior.COLOR
        .RANGE("G42").Resize(, 9).Interior.COLOR = srcWS.RANGE("G42").Resize(, 9).Interior.COLOR
        .RANGE("R42").Resize(, 7).Interior.COLOR = srcWS.RANGE("R42").Resize(, 7).Interior.COLOR
        .RANGE("AO42").Resize(, 7).Interior.COLOR = srcWS.RANGE("AO42").Resize(, 7).Interior.COLOR
        .RANGE("AX42").Resize(, 9).Interior.COLOR = srcWS.RANGE("AX42").Resize(, 9).Interior.COLOR
    End With
    With Sheets("AWelds")
        .RANGE("G12").Resize(, 9).Interior.COLOR = srcWS.RANGE("G12").Resize(, 9).Interior.COLOR
        .RANGE("R12").Resize(, 7).Interior.COLOR = srcWS.RANGE("R12").Resize(, 7).Interior.COLOR
        .RANGE("AO12").Resize(, 7).Interior.COLOR = srcWS.RANGE("AO12").Resize(, 7).Interior.COLOR
        .RANGE("AX12").Resize(, 9).Interior.COLOR = srcWS.RANGE("AX12").Resize(, 9).Interior.COLOR
        .RANGE("G14").Resize(, 9).Interior.COLOR = srcWS.RANGE("G14").Resize(, 9).Interior.COLOR
        .RANGE("R14").Resize(, 7).Interior.COLOR = srcWS.RANGE("R14").Resize(, 7).Interior.COLOR
        .RANGE("AO14").Resize(, 7).Interior.COLOR = srcWS.RANGE("AO14").Resize(, 7).Interior.COLOR
        .RANGE("AX14").Resize(, 9).Interior.COLOR = srcWS.RANGE("AX14").Resize(, 9).Interior.COLOR
        .RANGE("G21").Resize(, 9).Interior.COLOR = srcWS.RANGE("G21").Resize(, 9).Interior.COLOR
        .RANGE("R21").Resize(, 7).Interior.COLOR = srcWS.RANGE("R21").Resize(, 7).Interior.COLOR
        .RANGE("AO21").Resize(, 7).Interior.COLOR = srcWS.RANGE("AO21").Resize(, 7).Interior.COLOR
        .RANGE("AX21").Resize(, 9).Interior.COLOR = srcWS.RANGE("AX21").Resize(, 9).Interior.COLOR
        .RANGE("G23").Resize(, 9).Interior.COLOR = srcWS.RANGE("G23").Resize(, 9).Interior.COLOR
        .RANGE("R23").Resize(, 7).Interior.COLOR = srcWS.RANGE("R23").Resize(, 7).Interior.COLOR
        .RANGE("AO23").Resize(, 7).Interior.COLOR = srcWS.RANGE("AO23").Resize(, 7).Interior.COLOR
        .RANGE("AX23").Resize(, 9).Interior.COLOR = srcWS.RANGE("AX23").Resize(, 9).Interior.COLOR
        .RANGE("G30").Resize(, 9).Interior.COLOR = srcWS.RANGE("G30").Resize(, 9).Interior.COLOR
        .RANGE("R30").Resize(, 7).Interior.COLOR = srcWS.RANGE("R30").Resize(, 7).Interior.COLOR
        .RANGE("AO30").Resize(, 7).Interior.COLOR = srcWS.RANGE("AO30").Resize(, 7).Interior.COLOR
        .RANGE("AX30").Resize(, 9).Interior.COLOR = srcWS.RANGE("AX30").Resize(, 9).Interior.COLOR
        .RANGE("G32").Resize(, 9).Interior.COLOR = srcWS.RANGE("G32").Resize(, 9).Interior.COLOR
        .RANGE("R32").Resize(, 7).Interior.COLOR = srcWS.RANGE("R32").Resize(, 7).Interior.COLOR
        .RANGE("AO32").Resize(, 7).Interior.COLOR = srcWS.RANGE("AO32").Resize(, 7).Interior.COLOR
        .RANGE("AX32").Resize(, 9).Interior.COLOR = srcWS.RANGE("AX32").Resize(, 9).Interior.COLOR
        .RANGE("G39").Resize(, 9).Interior.COLOR = srcWS.RANGE("G39").Resize(, 9).Interior.COLOR
        .RANGE("R39").Resize(, 7).Interior.COLOR = srcWS.RANGE("R39").Resize(, 7).Interior.COLOR
        .RANGE("AO39").Resize(, 7).Interior.COLOR = srcWS.RANGE("AO39").Resize(, 7).Interior.COLOR
        .RANGE("AX39").Resize(, 9).Interior.COLOR = srcWS.RANGE("AX39").Resize(, 9).Interior.COLOR
        .RANGE("G41").Resize(, 9).Interior.COLOR = srcWS.RANGE("G41").Resize(, 9).Interior.COLOR
        .RANGE("R41").Resize(, 7).Interior.COLOR = srcWS.RANGE("R41").Resize(, 7).Interior.COLOR
        .RANGE("AO41").Resize(, 7).Interior.COLOR = srcWS.RANGE("AO41").Resize(, 7).Interior.COLOR
        .RANGE("AX41").Resize(, 9).Interior.COLOR = srcWS.RANGE("AX41").Resize(, 9).Interior.COLOR
    End With
    With Sheets("LoosePigtails")
        .RANGE("P12").Resize(, 2).Interior.COLOR = srcWS.RANGE("P12").Resize(, 2).Interior.COLOR
        .RANGE("Y12").Resize(, 16).Interior.COLOR = srcWS.RANGE("Y12").Resize(, 16).Interior.COLOR
        .RANGE("AV12").Resize(, 2).Interior.COLOR = srcWS.RANGE("AV12").Resize(, 2).Interior.COLOR
        .RANGE("P14").Resize(, 2).Interior.COLOR = srcWS.RANGE("P14").Resize(, 2).Interior.COLOR
        .RANGE("Y14").Resize(, 16).Interior.COLOR = srcWS.RANGE("Y14").Resize(, 16).Interior.COLOR
        .RANGE("AV14").Resize(, 2).Interior.COLOR = srcWS.RANGE("AV14").Resize(, 2).Interior.COLOR
        .RANGE("P21").Resize(, 2).Interior.COLOR = srcWS.RANGE("P21").Resize(, 2).Interior.COLOR
        .RANGE("Y21").Resize(, 16).Interior.COLOR = srcWS.RANGE("Y21").Resize(, 16).Interior.COLOR
        .RANGE("AV21").Resize(, 2).Interior.COLOR = srcWS.RANGE("AV21").Resize(, 2).Interior.COLOR
        .RANGE("P23").Resize(, 2).Interior.COLOR = srcWS.RANGE("P23").Resize(, 2).Interior.COLOR
        .RANGE("Y23").Resize(, 16).Interior.COLOR = srcWS.RANGE("Y23").Resize(, 16).Interior.COLOR
        .RANGE("AV23").Resize(, 2).Interior.COLOR = srcWS.RANGE("AV23").Resize(, 2).Interior.COLOR
        .RANGE("P30").Resize(, 2).Interior.COLOR = srcWS.RANGE("P30").Resize(, 2).Interior.COLOR
        .RANGE("Y30").Resize(, 16).Interior.COLOR = srcWS.RANGE("Y30").Resize(, 16).Interior.COLOR
        .RANGE("AV30").Resize(, 2).Interior.COLOR = srcWS.RANGE("AV30").Resize(, 2).Interior.COLOR
        .RANGE("P32").Resize(, 2).Interior.COLOR = srcWS.RANGE("P32").Resize(, 2).Interior.COLOR
        .RANGE("Y32").Resize(, 16).Interior.COLOR = srcWS.RANGE("Y32").Resize(, 16).Interior.COLOR
        .RANGE("AV32").Resize(, 2).Interior.COLOR = srcWS.RANGE("AV32").Resize(, 2).Interior.COLOR
        .RANGE("P39").Resize(, 2).Interior.COLOR = srcWS.RANGE("P39").Resize(, 2).Interior.COLOR
        .RANGE("Y39").Resize(, 16).Interior.COLOR = srcWS.RANGE("Y39").Resize(, 16).Interior.COLOR
        .RANGE("AV39").Resize(, 2).Interior.COLOR = srcWS.RANGE("AV39").Resize(, 2).Interior.COLOR
        .RANGE("P41").Resize(, 2).Interior.COLOR = srcWS.RANGE("P41").Resize(, 2).Interior.COLOR
        .RANGE("Y41").Resize(, 16).Interior.COLOR = srcWS.RANGE("Y41").Resize(, 16).Interior.COLOR
        .RANGE("AV41").Resize(, 2).Interior.COLOR = srcWS.RANGE("AV41").Resize(, 2).Interior.COLOR
    End With
    With Sheets("TeeDowncomer")
        For x = 1 To 4
            .RANGE("AF" & y).Interior.COLOR = srcWS.RANGE("AF" & y).Interior.COLOR
            y = y + 9
        Next x
    End With
    y = 13
    With Sheets("Headers")
        .RANGE("G13").Resize(, 25).Interior.COLOR = srcWS.RANGE("G13").Interior.COLOR
        .RANGE("AH13").Resize(, 25).Interior.COLOR = srcWS.RANGE("AH13").Interior.COLOR
        .RANGE("G22").Resize(, 25).Interior.COLOR = srcWS.RANGE("G22").Interior.COLOR
        .RANGE("AH22").Resize(, 25).Interior.COLOR = srcWS.RANGE("AH22").Interior.COLOR
        .RANGE("G31").Resize(, 25).Interior.COLOR = srcWS.RANGE("G31").Interior.COLOR
        .RANGE("AH31").Resize(, 25).Interior.COLOR = srcWS.RANGE("AH31").Interior.COLOR
        .RANGE("G40").Resize(, 25).Interior.COLOR = srcWS.RANGE("G40").Interior.COLOR
        .RANGE("AH40").Resize(, 25).Interior.COLOR = srcWS.RANGE("AH40").Interior.COLOR
    End With
    Application.ScreenUpdating = True
End Sub
Click to expand...
When using this code, I changed one color from the Sheet 1 G11 to Yellow, ran the code, and it changed Sheet 2 G11:O11 to black.
 
Upvote 0
Will all the consecutive groups of cells, for example G11:O11, be the same colour or can each cell in the group be a different colour?
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Question Question
Copy Range from Multiple Sheets and Paste Link into a Single Sheet
Replies
2
Views
339
vbascientist
V
S
  • Solved
count condition cell color
Replies
5
Views
194
Trebor76
T
H
  • Solved
Change bold font using VBA
Replies
3
Views
117
Peter_SSs
Peter_SSs
S
  • Solved
Sheets selection based on tab color
Replies
2
Views
85
SoerenB
S
M
  • Solved
cut/copy and paste row based on cell value not being equal to specific text
Replies
6
Views
624
mlan1604
M

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top