If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
conditional formatting in form slows down calculations | Susan | Using Forms | 5 | June 13th, 2005 11:24 AM |
Conditional Formatting Error | ddate | Worksheet Functions | 0 | May 5th, 2005 09:00 PM |
difficulty with conditional formatting | Deb | General Discussion | 0 | March 23rd, 2005 06:13 PM |
Conditional formatting over a picture | Carl Rapson | Setting Up & Running Reports | 0 | December 27th, 2004 04:21 PM |
Required Field Conditional | Katherine R | New Users | 2 | September 1st, 2004 05:52 AM |