Way to reference a number in a cell

[acertco03dis]

Currently I have a cell that says "number of cars:2". I want to write something that makes it so that if the number of cars is less than 2, turn the cell red. If it is greater than 2, turn the cell green. Is there a way to reference just the number in the cell when writing an if statement. If I can, how would I do so? Thanks.

[JKwan]

well, give this a spin


Sub test()
Dim vData As Variant

vData = Split(Range("A1"), ":")
Select Case Val(Trim(vData(1)))
Case Is < 2
Range("A1").Interior.ColorIndex = 3

Case Is > 2
Range("A1").Interior.ColorIndex = 4

Case Else
MsgBox "your car is 2 - not doing anything"
End Select
End Sub

[mdmackillop]

Combine a UDF with Conditional Formatting

Function Car(data)
Car = CLng(Split(data, ":")(1))
End Function