6 or more conditional formatting

[Guest]

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

 

[Guest]

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,

 

[David McRitchie]

Hi Gary,
A change event macro usually addresses one cell (target) that is changed,
but by using the entire used range, you've covered values that
get changed in formulas as soon as a constant value is changed, or content
removed; however, unless the sheet is getting values from another sheet that
would make the Application.Volatile redundant. .

The macro will not revert to "no fill" for cells that no longer have one of the
values tested, so suggest first clearing out interior color for entire worksheet. with
cells.ColorIndex = xlNone
which is more efficient that doing one cell at a time and would remove coloring
outside of the used range.

VBA is case sensitive for most items so suggest
varValue = LCase(rngCell.Value)

On a laptop you probably want to use pastel colors to be able to view conten
anywhere on the screen, in any case one can find the color index colors
in the VBE Help (Colorindex Property) or on either
http://www.mvps.org/dmcritchie/excel/colors.htm
http://www.mvps.org//dmcritchie/excel/event.htm#case

So the changes that I would suggest are in the following area:

' On Error Resume Next '-- don't see a reason why included
' Application.Volatile '-- not likely to be needed
Set rngUsed = ActiveSheet.UsedRange
cells.ColorIndex = xlNone
For Each rngCell In rngUsed
varValue = LCase(rngCell.Value)

Question:
I know that numbers default to gray if not already getting a color but what is being
tested for True. I can understand zero and empty cells as not testing for True,
but why not text, and since numbers are treated as True why are dates and time
not also treated as True since they are numeric.
-
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm