I am assuming that you want to count cells like:

=IF(Sheet1!B2=0,"RED","PURPLE") that display RED, etc.

The following code will count such cells:

Sub Macro1()
Dim r As Range
Dim count As Long
Dim s1 As String
Dim s2 As String

count = 0
s2 = "Sheet1"

For Each r In Selection
If r.Value = "RED" Or r.Value = "GREEN" Or r.Value = "YELLOW" Then
s1 = r.Formula
If InStr(1, s1, s2) 0 Then
count = count + 1
End If
End If
Next
MsgBox (count)
End Sub


On the worksheet, just select the cells you want to check and run the macro.
--
Gary's Student


"Barb Reinhardt" wrote:

I'm not sure this is going to get me exactly what I want. I want to check
the formulas as follows:

Cell I5 formula should contain Sheet1
Cell I6 formula should contain Sheet2
Cell I7 formula should contain Sheet3

Etc. The sheet names will be different in my case, but this gives an
example of what I want.

The other will definitely be useful in the future though.
"Tom Ogilvy" wrote:

Turn on the macro recorder, then do
Edit=Find
and search for you sheet name. Specify to look in formulas.

Now turn off the macro recorder.

This is an easy way to get the parameters you need for the find command.

Now go to VBA and use the help on the FindNext command. Look at the visual
basic example. It shows how to find all instances. Combine that with the
code you recorded.

Now just add a check for the value of the cell being red, yellow or green.

--
Regards,
Tom Ogilvy


"Barb Reinhardt" wrote:

I hope I can explain this so that it makes sense.

I have a summary sheet that gets data off of individual worksheets. I want
to check for the following:

Cell Value = RED, GREEN or YELLOW
Formula in the cell contains a specific worksheet name (let's use Sheet1 for
this example)
Sheet 1 is present.

If all of these conditions are met, the count would increment by one. Can
someone help me get started on this. Once I have an idea where to start, I
can do the rest of it.

Thanks in advance for your assistance.

Regards,
Barb Reinhardt