macro to copy the colour format

[excelhelpseeker]

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

[p45cal]

try:

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)

[p45cal]

or if it really is only the fill you want to copy over:

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)

[excelhelpseeker]

Thanks a TON... you saved my day.