Need help with code.
 

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

Need help with code.
 

Damien,
Depending on where your list of colours is coming from, here's one way:

Private Sub CommandButton1_Click()
Const AllColours As String = "Red,Yellow,Green"
Dim ColourValues As Variant
Dim RetVal As Long
Dim i As Long
On Error Resume Next
ColourValues = Split(AllColours, ",")
With Sheets("Form")
For i = 0 To UBound(ColourValues)
RetVal = Application.WorksheetFunction.Match(ColourValues(i ),
..Range("$C$37:$C$47"), 0)
If Err.Number = 0 Then
.Range("$C$49").Value = ColourValues(i)
Exit Sub
End If
Err.Clear
Next
.Range("$C$49").Value = "No Match"
End With
End Sub

Using Find or VLookUp would be similar.
You could achieve the same directly on the worksheet, with Match or VLookUp,
or write a UDF similar to the above, passing in 2 argument, rangetosearch,
rangeofcolors. e.g
Public Function MatchColour(argRangeToSearch As Range, argRangeOfColours As
Range) As String
....etc
then call it from the sheet in C49, assuming you colours in range("E1:E3")
=MatchColour(C37:C47,E1:E3)

Obviously, your list of colours have to arranged in the order of importance.

NickHK

"Damien" wrote in message
...
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

Need help with code.
 

Nick,

Thanks a million, the code works a treat!

"NickHK" wrote:

Damien,
Depending on where your list of colours is coming from, here's one way:

Private Sub CommandButton1_Click()
Const AllColours As String = "Red,Yellow,Green"
Dim ColourValues As Variant
Dim RetVal As Long
Dim i As Long
On Error Resume Next
ColourValues = Split(AllColours, ",")
With Sheets("Form")
For i = 0 To UBound(ColourValues)
RetVal = Application.WorksheetFunction.Match(ColourValues(i ),
..Range("$C$37:$C$47"), 0)
If Err.Number = 0 Then
.Range("$C$49").Value = ColourValues(i)
Exit Sub
End If
Err.Clear
Next
.Range("$C$49").Value = "No Match"
End With
End Sub

Using Find or VLookUp would be similar.
You could achieve the same directly on the worksheet, with Match or VLookUp,
or write a UDF similar to the above, passing in 2 argument, rangetosearch,
rangeofcolors. e.g
Public Function MatchColour(argRangeToSearch As Range, argRangeOfColours As
Range) As String
....etc
then call it from the sheet in C49, assuming you colours in range("E1:E3")
=MatchColour(C37:C47,E1:E3)

Obviously, your list of colours have to arranged in the order of importance.

NickHK

"Damien" wrote in message
...
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