Home |
Search |
Today's Posts |
#1
|
|||
|
|||
6 or more conditional formatting
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 "Gary L Brown" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting Error | Excel Worksheet Functions | |||
difficulty with conditional formatting | Excel Discussion (Misc queries) | |||
conditional formatting question | Excel Discussion (Misc queries) | |||
Determine cells that drive conditional formatting? | Excel Discussion (Misc queries) | |||
Conditional formatting not available in Excel | Excel Discussion (Misc queries) |