I have a macro that when you click a button the cells that are gray will turn
white and the text that is blue will turn white, then I copy the cells and
paste them into word.

The cells can turn white if I delete the copy and paste code, but it will
not work with this code. Can someone help?



Private Sub CommandButton1_Click()
Worksheets("Report").Unprotect
Dim rng As Range, c As Range
Set rng = Selection
For Each c In rng
If c.Interior.ColorIndex = 15 Then
c.Interior.ColorIndex = 2
End If
Next c
For Each c In rng
If c.Font.ColorIndex = 5 Then
c.Font.ColorIndex = 2
End If
Next c

Range("A1:J9769").Select
Selection.Copy
Dim WDApp As Object
Dim WDDoc As Object
Dim myDocName As String

myDocName = "Survey Report.doc"

'Open Word and add a new document
Set WDApp = CreateObject("Word.Application")
WDApp.Visible = True
WDApp.Activate
Set WDDoc = WDApp.Documents.Add

WDDoc.Range.Paste
With WDDoc.Tables(1)
WDDoc.Range.Tables(1).Rows.Alignment = wdAlignRowCenter
End With
With WDDoc.PageSetup
.LineNumbering.Active = False
.Orientation = wdOrientPortrait
.TopMargin = InchesToPoints(0.5)
.BottomMargin = InchesToPoints(0.5)
.LeftMargin = InchesToPoints(0.75)
.RightMargin = InchesToPoints(0.75)
.Gutter = InchesToPoints(0)
.HeaderDistance = InchesToPoints(0.5)
.FooterDistance = InchesToPoints(0.5)
.PageWidth = InchesToPoints(8.5)
.PageHeight = InchesToPoints(11)

End With

Worksheets("Report").Protect
ws_exit:
Application.EnableEvents = True
End Sub

Thank you,

Daniel Young