Color formatting when searching excel

Question

I currently have a macro that will search an excel spreadsheet and then place the results in a different sheet. The only issue is is that it wont copy the color formatting when transferring the results. Here is the code. I have tried paste special but it doesnt work for all the results.

Sub Searchcustomer()    
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim ws As Worksheet
    Set ws = wb.Sheets("Search")

    If ws.Range("B3").Value = "" And ws.Range("B4").Value = "" And ws.Range("B5").Value = "" And ws.Range("B6").Value = "" And ws.Range("B7").Value = "" And ws.Range("B8").Value = "" Then
                MsgBox "Please Enter Data into Table"
                Exit Sub
    End If

    Dim msheet As Worksheet
    Dim ssheet As Worksheet
    Dim stand  As String
    Dim number As String
    Dim customer As String
    Dim states As String
    Dim find As String
    Dim audit As String
    Dim saudit As String
    Dim est As String
    Dim pub As String
    Dim finalrow As Integer
    Dim finalrow2 As Integer
    Dim i As Integer

    Set msheet = Sheet4
    Set ssheet = Sheet5

    number = ssheet.Range("B3").Value
    customer = ssheet.Range("B4").Value
    states = ssheet.Range("B5").Value
    find = ssheet.Range("B6").Value
    audit = ssheet.Range("B7").Value
    saudit = ssheet.Range("B8").Value

    msheet.Select
    finalrow = msheet.Cells(Rows.Count, 1).End(xlUp).Row

    For i = 1 To finalrow
        If IIf(stand <> "", msheet.Cells(i, 1) = number, True) And IIf(number <> "", msheet.Cells(i, 2) = number, True) And IIf(customer <> "", msheet.Cells(i, 3) = customer, True) And IIf(states <> "", Cells(i, 4) = states, True) And IIf(find <> "", Cells(i, 5) = find, True) And IIf(audit <> "", Cells(i, 6) = audit, True) And IIf(saudit <> "", Cells(i, 7) = saudit, True) And IIf(est <> "", msheet.Cells(i, 8) = number, True) And IIf(pub <> "", msheet.Cells(i, 9) = number, True) Then
            msheet.Range(msheet.Cells(i, 1), msheet.Cells(i, 9)).Copy
            ssheet.Range("A100").End(xlUp).Offset(1, 0).Resize(1, 9).Value = msheet.Range(msheet.Cells(i, 1), msheet.Cells(i, 9)).Value

        End If
    Next i

    ssheet.Select
    ssheet.Range("B3").Select
End Sub

Answer 1

Note that you if you use

ssheet.Range("A100").End(xlUp).Offset(1, 0).Resize(1, 9).Value = msheet.Range(msheet.Cells(i, 1), msheet.Cells(i, 9)).Value

it olny transfers the value but no formattings.

And if you use .Copy you need to specify the destination. You didn't and therefore your .Copy line does nothing at all. * See Mathieu's comment below.

Throw out the .Value line and replace the .Copy line with the following:

msheet.Range(msheet.Cells(i, 1), msheet.Cells(i, 9)).Copy Destination:=ssheet.Range("A100").End(xlUp).Offset(1, 0).Resize(1, 9)

---

Note that row counting variables must be of type Long Excel has more rows than Integer can handle!

Dim finalrow As Long
Dim finalrow2 As Long
Dim i As Long