Use the change event:

Chip Pearson's page on Events
http://www.cpearson.com/excel/events.htm


Private Sub Worksheet_Change(ByVal Target As Range)
Dim v as Variant
v = Array(3, 4,5,8,6)
if not Intersect(target,Range("B9:B20")) is nothing then
if Target.Value 0 and Target.Value < 6
Target.Interior.ColorIndex = v(Target.Value-1)
end if
end if
End Sub

--
Regards,
Tom Ogilvy


Right click on the sheet tab and select view code. Put in code like the
above.
"viksha" wrote in message
...
Hi,

I have defined a dropdown, by using a DataValidationList..

Now it has got 5 values say 1 ,2 ,3, 4 and 5

Now my requirement is , I wnat to change the color of the cell depending
on
the value I select from the text.say if 1 is selected then it should be
red
and 2 is selected it should be greeen and so on..

I tried using FORMATCONTIONAL..but this is restricted to only 4 , I have
condtion to check for more than 4 say 5 or 6..

Looking forward for reply.
--
Programmin