macro help, copying font and color

[airfrm]

Hoping for some help with macros. I have 2 sheets in a workbook. the second sheet is copied from the "yesterday" with notes and some of those notes are in bold and different font colors. when I run the macro I'm wanting to keep the font from the day prior to carry over to the current day without effecting any new notes. It is copying all the info correctly except the bold or any font colors.


ElseIf Len(ActiveSheet.Cells(x, 2)) = 0 And Len(ActiveSheet.Cells(x, 3)) <> 0 Then 'Gripe row

ActiveSheet.Cells(x, lastcol).HorizontalAlignment = xlLeft 'set horiz alignment for remarks
ActiveSheet.Cells(x, 3).HorizontalAlignment = xlLeft 'nomenclature

'import yesterday's notes
vReturn = Application.Match(Range("L" & x).Value, Worksheets("Yesterday").Range("L:L"), 0)
If IsError(vReturn) Then
Range("B" & x).Value = " "
Else
ActiveSheet.Range("B" & x).Value = Application.Index(Worksheets("Yesterday").Range("B:B"), vReturn)
Worksheets("Yesterday").Range("B" & vReturn).Copy
ActiveSheet.Range("B" & x).PasteSpecial xlPasteFormats
ActiveSheet.Range("B" & x).Font.Name = "Arial"
ActiveSheet.Range("B" & x).Font.Size = 11

 

[sijpie]

Your code in VBA code brackets (please use this when posting code, click the little VBA icon above the post area):

lseIf Len(ActiveSheet.Cells(x, 2)) = 0 And Len(ActiveSheet.Cells(x, 3)) <> 0 Then 'Gripe row

ActiveSheet.Cells(x, lastcol).HorizontalAlignment = xlLeft 'set horiz alignment for remarks
ActiveSheet.Cells(x, 3).HorizontalAlignment = xlLeft 'nomenclature

'import yesterday's notes
vReturn = Application.Match(Range("L" & x).Value, Worksheets("Yesterday").Range("L:L"), 0)
If IsError(vReturn) Then
    Range("B" & x).Value = " "
Else
    ActiveSheet.Range("B" & x).Value = Application.Index(Worksheets("Yesterday").Range("B:B"), vReturn)
    Worksheets("Yesterday").Range("B" & vReturn).Copy
    ActiveSheet.Range("B" & x).PasteSpecial xlPasteFormats
    ActiveSheet.Range("B" & x).Font.Name = "Arial"
    ActiveSheet.Range("B" & x).Font.Size = 11

if vReturn has the correct value for the column with the correct formatting, then the macro should copy the formats.

But have you checked to see if vReturn has the correct value?
In the VBA editor click on the grey border just to the left of the line

vReturn = Application.Match(Range("L" & x).Value, Worksheets("Yesterday").Range("L:L"), 0)

The line should turn dark red, you have set a breakpoint here. When you now run the macro, the macro will stop here for you to debug.
You will see the line with the code in yellow, showing this is the next line to be executed.
press the F8 key once, this will execute the line.
now hover your mouse over vReturn. You should see the value that it now has. Is this the correct cell to take the format from?