Results 1 to 4 of 4

Thread: macro to copy the colour format

  1. #1

    macro to copy the colour format



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi Champs,

    i Have dynamic range in A column which has serial number with blank cells underneath it.


    For example, the serial in A3 range has 4 blank cells underneath it A3:A7, but coloured only from A3:A5...... i have many example like this .... i need a macro to copy the format only (colour) until the next serial number.. pls see the sample file for the reference .. input and output.sample.xlsx

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,844
    Articles
    0
    Excel Version
    365
    try:
    Code:
    Sub blah()
    For Each are In Columns("A:A").SpecialCells(xlCellTypeBlanks).Areas
      are.Cells(1).Offset(-1).Copy
      are.PasteSpecial Paste:=xlPasteFormats
    Next are
    Application.CutCopyMode = False
    End Sub
    (will crash if there's a blank in cell A1)

  3. #3
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,844
    Articles
    0
    Excel Version
    365
    or if it really is only the fill you want to copy over:
    Code:
    Sub blah2()
    For Each are In Columns("A:A").SpecialCells(xlCellTypeBlanks).Areas
      With are.Interior
      Set zzz = are.Cells(1).Offset(-1).Interior
        .Pattern = zzz.Pattern
        .PatternColorIndex = zzz.PatternColorIndex
        .Color = zzz.Color
        .TintAndShade = zzz.TintAndShade
        .PatternTintAndShade = are.Cells(1).Offset(-1).Interior.PatternTintAndShade
      End With
    Next are
    End Sub
    (will still crash if there's a blank in cell A1)
    Last edited by p45cal; 2015-02-14 at 06:00 PM.

  4. #4
    Thanks a TON... you saved my day.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •