What is wrong with this code? I keep getting a Type Mismatch error. I am
trying to match the words red, yellow and green which appear in 6 cells above
(lets say C37:C47). A cell below (C49) is to show the word red if in fact
'red' appears in any the 6 cells. However if the word red does not appear and
the word yellow appears I need the cell below to display the word yellow.
Also if the words red and yellow do not appear I need the word green to
appear in the cell below.

Any help would be greatly appreciated (still a greenhorn at this)


Private Sub CommandButton1_Click()

If Sheets("Form").Range("$C$37:$C$47").Value = "Red" Then
Sheets("Form").Range("$C$49").Value = "Red"
End If
If Sheets("Form").Range("$C$37:$C$47").Value = "Yellow" Then
Sheets("Form").Range("$C$49").Value = "Yellow"
End If
If Sheets("Form").Range("$C$37:$C$47").Value = "Green" Then
Sheets("Form").Range("$C$49").Value = "Green"
End If

End Sub