retrieve .values and their formatting from source

[XL_NOOB_89]

I see a ton of posts on this topic, but none of them are actually what I'm struggling with. The code below works fine, but it only carries over the values of the source cell (.values). I'm looking to also carry over the source formatting from the Input sheet - the source formatting will be dynamic. I don't want to use the .copy/.pastespecial command. Majority of the source cells on the Input sheet are text, but can also be numbers at times.

I'm basically running through a massive list on the Input sheet and copying everything over to the Output sheet with a row in between each line. I've tried multiple variations in the With clause, but nothing seems to work. Any help would be appreciated:

Sub DataRetrieval()

Dim LR As Long, i As Long
Dim wsInput As Worksheet
Dim wsOutput As Worksheet
Dim lCount As Long
Dim CommRow As Long
Dim TheMark As Range

Set wsInput = ThisWorkbook.Worksheets("Input")
Set wsOutput = ThisWorkbook.Worksheets("Email Output")
Set TheMark = wsInput.Range("C3")

wsOutput.Range("C9:C1000").ClearContents
wsOutput.Range("C9:C1000").ClearFormats

CommRow = 9

For lCount = 1 To WorksheetFunction.CountIf(wsInput.Columns(3), "*")

Set TheMark = wsInput.Columns(3).Find(what:="*", after:=TheMark, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
   
With TheMark
        wsOutput.Range("C" & CommRow).Value = TheMark
End With

    CommRow = CommRow + 2
   
Next lCount

End Sub

 

[NateSC]

If you don't want to copy and paste, I think you have to set each piece of the format separately.

wsOutput.Range("C" & CommRow).Font.size = TheMark.Font.size
wsOutput.Range("C" & CommRow).Font.bold= TheMark.Font.bold
wsOutput.Range("C" & CommRow).interior.color = TheMark.interior.color

etc. - I just put a couple of options in there.

I'm not aware of an all in one format property that would get them all in one shot, however I could be wrong.

 

[rlv01]

I don't want to use the .copy/.pastespecial command.

Why not?

 

[XL_NOOB_89]

If you don't want to copy and paste, I think you have to set each piece of the format separately.

wsOutput.Range("C" & CommRow).Font.size = TheMark.Font.size
wsOutput.Range("C" & CommRow).Font.bold= TheMark.Font.bold
wsOutput.Range("C" & CommRow).interior.color = TheMark.interior.color

etc. - I just put a couple of options in there.

I'm not aware of an all in one format property that would get them all in one shot, however I could be wrong.

This seems to be what I need. Thank you!