PDA

View Full Version : Way to reference a number in a cell



acertco03dis
06-16-2016, 08:16 AM
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
06-16-2016, 10:52 AM
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
06-16-2016, 11:09 AM
Combine a UDF with Conditional Formatting

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