Here's a macro that will allow as many different values as you want...

'/======================================/
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngUsed As Range, rngCell As Range
Dim varValue As Variant

On Error Resume Next
Application.Volatile
Set rngUsed = ActiveSheet.UsedRange

For Each rngCell In rngUsed
varValue = rngCell.Value
Select Case varValue
Case "apple"
rngCell.Interior.ColorIndex = 3 'red
Case "banana"
rngCell.Interior.ColorIndex = 6 'yellow
Case "mango"
rngCell.Interior.ColorIndex = 10 'green
Case "pineapple"
rngCell.Interior.ColorIndex = 46 'orange
Case "guava"
rngCell.Interior.ColorIndex = 1 'black
Case "grape"
rngCell.Interior.ColorIndex = 13 'violet
Case "ice cream"
rngCell.Interior.ColorIndex = 5 'blue
Case 10, 200, 1000
rngCell.Interior.ColorIndex = 53 'brown
Case True
rngCell.Interior.ColorIndex = 15 'gray
End Select
Next rngCell

Set rngUsed = Nothing

End Sub
'/======================================/
HTH,
--
Gary Brown

If this post was helpful, please click the ''''''''Yes'''''''' button next
to ''''''''''''''''Was this Post Helpfull to you?".


"Conditional Formatting" wrote:

Please give me sample of macro with 6 or more conditional formatting using
the drop down boxes
for example
apple cell turns to red
banana cell turns to yellow
mango cell turns to yellow green
pineaple cell turns to orange
guava cell turns to blak
grape cell turns to violet

Thanks