Announcement

Collapse
No announcement yet.

Macro for copying font color

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Macro for copying font color



    Hi Guys,

    I need a macro which will copy exact format (font color) from cells in sheet2 to cells in sheet1. Please note that number of parts change depend on request from my customers so I really appreciate if macro will be universal. Thank you in advance
    Attached Files

  • #2


    Hi

    Try the following:

    Code:
    Sub Test()
        Dim wsTarget As Worksheet
        Dim wsSource As Worksheet
        Dim rngTarget As Range
        Dim rngSource As Range
        Dim c As Range, SourceCell As Range
        
        Application.ScreenUpdating = False
        'On Error Resume Next
        Set wsTarget = Sheet1
        Set wsSource = Sheet2
        
        With wsTarget
            x = .Rows.Count
            Set rngTarget = .Range("A1", .Range("A" & x).End(xlUp))
            
        End With
    
        With wsSource
            Set rngSource = .Range("A1", .Range("A" & x).End(xlUp))
        End With
           
        With rngSource
            For Each c In rngTarget
                Set SourceCell = .Find(c.Value, , xlValues, xlWhole)
                
                If Not SourceCell Is Nothing Then
                    SourceCell.Copy
                    c.PasteSpecial xlPasteFormats
                Else
                   MsgBox c.Value & " Not Found in source.", vbInformation, "Copy Format"
                End If
            Next
        End With
        
        Application.ScreenUpdating = True
    End Sub
    Regards

    Maqbool

    Comment

    Working...
    X