Results 1 to 4 of 4

Need the cell background color transfered along with data in this Macro

Threaded View

  1. #1
    Registered User
    Join Date
    05-19-2010
    Location
    Owings Mills, USA
    MS-Off Ver
    Excel 2007
    Posts
    13

    Question Need the cell background color transfered along with data in this Macro

    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.
    Attached Files Attached Files
    Last edited by sureng19; 10-16-2010 at 12:39 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1