Hello Everyone! Is there any way to have the original cell background color also transfered/pasted along with the cell values.
Attached is a worksheet sample with macro, showing the actual output (can be rerun) and the desired output for clarification.
Sub CreateCommentsSummary()
Dim rgComments As Range, rgCell As Range, rgOutput As Range, iRow As Integer, iCol As Integer
Dim strSearch As String
Set rgComments = ActiveSheet.Cells.SpecialCells(xlCellTypeComments)
Set rgOutput = Range("Sheet1!E2") ' output range fixed for example
iRow = rgOutput.Row
iCol = rgOutput.Column
' read each cell with comment and build the summary
For Each rgCell In rgComments
If InStr(rgCell.Comment.Text, strSearch) > 0 Then
Sheets(1).Cells(iRow, iCol) = rgCell.Address ' print cell address
Sheets(1).Cells(iRow, iCol + 1) = rgCell.value ' print cell value
Sheets(1).Cells(iRow, iCol + 2) = rgCell.Comment.Text ' print cell comment text
iRow = iRow + 1
End If
Next rgCell
End Sub
Thanks in advance.
Bookmarks