Condensing my IF statement

[mr.mackey]

Hello, as I am new to VBA programming, I am not so good at writing concise code (obvious from what you will see below).

Can you please help me condense the following code? My range of values are C19:R19, and C55:R55.


If Range("C19") = "CAR" And (Range("C55") = "--" Or Range("C55") = "YELLOW" Or Range("C55") = "BLACK" Or Range("C55") = "RED") Then
MsgBox "The colour of your car is not valid."
Exit Sub
End If

If Range("D19") = "CAR" And (Range("D55") = "--" Or Range("D55") = "YELLOW" Or Range("D55") = "BLACK" Or Range("D55") = "RED") Then
MsgBox "The colour of your car is not valid."
Exit Sub
End If
.
.
.
.
.
.

If Range("R19") = "CAR" And (Range("R55") = "--" Or Range("R55") = "YELLOW" Or Range("R55") = "BLACK" Or Range("R55") = "RED") Then
MsgBox "The colour of your car is not valid."
Exit Sub
End If

 

[Jonmo1]

Try

Dim C As Range
For Each C In Range("C19:R19")
    If UCase(C) = "CAR"
        Select Case UCase(Cells(55, C.Column))
            Case "--", "YELLOW", "BLACK", "RED"
                MsgBox "The colour of your car is not valid."
                Exit Sub
        End Select
    End If
Next C

Hope that helps.

 

[mr.mackey]

It gives me a Syntax Error on the line

If UCase(C) = "CAR"

 

[Jonmo1]

Ugh!!

It's too early....

That should be

If UCase(C) = "CAR" Then

 

[mr.mackey]

Thanks, this has been extremely helpful.

I noticed while playing around with the code, that if I use , for example "Yellow" as opposed to "YELLOW" in the code, that msgbox won't appear even if the cell is equal to Yellow.

Why does the value of yellow in the code have to be all in caps for it to work?

 

[Jonmo1]

Because of the Ucase(C).

That capitalizes whatever is in the Cell.
Then compares it to "YELLOW"
This makes it NOT Case Sensitive.

So regardless of the capitalization of the value in the cell, the Ucase converts it to all caps and compares it to "YELLOW"


So leave it as all caps in the code
It will not matter how it's capitalized in the cell.