One way would be to loop through each cell in the range something like this:

dim cell as range
For each cell in ActiveSheet.Range("A1:J9769")
if cell.value = "No Photo" Then
cell.Font.ColorIndex = 2
if cell.row 3 then
Selection(-3, 2).Interior.ColorIndex = 2
end if
end if
next cell

this may be quite slow given the size of the range so another option may
be to apply conditional formatting eg:

With Range("A1:J9769")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""No Photo"""
.FormatConditions(1).Font.ColorIndex = 2
End With

Regards
Rowan


Daniel R. Young wrote:
I have a very long survey that has been created into a report in excel. When
a person wants to print out the document, I would like them to click the
macro button "print report", then all cells that say no photo will change
font color from blue to white (appear hidden) and the cells around them that
are gray will turn to white. Finally I would like all the visible rows (some
rows are hidden) to copy and paste into a word docuement.

I hope this makes sense.


"Rowan" wrote:


A range, eg ActiveSheet.Range("A1:J9769"), can't equal a string, eg "No
Photo". What are you trying to do here?

Rowan

PS Selection(-3, 2).Interior.ColorIndex = 2 will also fail. Probably
should be Selection.offset(-3, 2).Interior.ColorIndex = 2

Daniel R. Young wrote:

I am getting a new error now: Run-time error '13': Type mismatch with this
new code:

Private Sub Commandbutton1_click()
If ActiveSheet.Range("A1:J9769") = "No Photo" Then
Selection.Font.ColorIndex = 2
Selection(-3, 2).Interior.ColorIndex = 2
Range("A1:J9769").Select
Selection.Copy
Dim WDApp As Object
Dim WDDoc As Object
Dim myDocName As String

myDocName = "Survey Report.doc"

Set WDApp = CreateObject("Word.Application")
WDApp.Visible = True

Set WDDoc = WDApp.documents.Open("F:\Survey Test\Word\Survey Report.doc")

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