Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting cell background color based on value
Hi
I am trying to set an array of cells' background colors based on their individual text values. Conditional formatting would normally work, but I have 10 different colors to assign and the built in conditional formatting only allows three conditions. Hoping someone could help me with this little project. Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting cell background color based on value
You can use the change event with Select Case
Here is a example that use the Change event of the worksheet Right click on a sheet tab and choose view code. Paste the event in there and press Alt-Q to go back to Excel. this will only work in a1:a20 Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("a1:a20"), Target) Is Nothing Then Select Case Target.Value Case "a" Target.Interior.ColorIndex = 3 Case "b" Target.Interior.ColorIndex = 5 Case "c" Target.Interior.ColorIndex = 8 Case Else Target.Interior.ColorIndex = xlNone End Select End If End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Erik" wrote in message ... Hi, I am trying to set an array of cells' background colors based on their individual text values. Conditional formatting would normally work, but I have 10 different colors to assign and the built in conditional formatting only allows three conditions. Hoping someone could help me with this little project. Thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting cell background color based on value
Erik
Private Sub Worksheet_Change(ByVal Target As Range) Dim Num As Long Dim rng As Range Dim vRngInput As Variant Set vRngInput = Intersect(Target, Range("A:A")) If vRngInput Is Nothing Then Exit Sub For Each rng In vRngInput 'Determine the color Select Case rng.Value Case Is = 1: Num = 6 'yellow Case Is = 2: Num = 10 'green Case Is = 3: Num = 5 'blue Case Is = 4: Num = 3 'red Case Is = 5: Num = 46 'orange End Select 'Apply the color rng.Interior.ColorIndex = Num Next rng End Sub Change the Case Is = 1 etc to Case Is = "yourtext" etc. Gord Dibben Excel MVP On Wed, 25 Feb 2004 09:16:13 -0800, "Erik" wrote: Hi, I am trying to set an array of cells' background colors based on their individual text values. Conditional formatting would normally work, but I have 10 different colors to assign and the built in conditional formatting only allows three conditions. Hoping someone could help me with this little project. Thanks. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting cell background color based on value
Ron and Gord
Thank you. Both sets of code work great. One more question. How can I apply the same background color to an adjacent cell regardless of that cell's value Erik |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Sum Based on Cell Background Color | Excel Worksheet Functions | |||
Change background color based on cell content from link | Excel Discussion (Misc queries) | |||
change the color of cell background based on a result | Excel Worksheet Functions | |||
Changing background color based on different cell | Excel Discussion (Misc queries) | |||
Conditionally setting background color of a cell | Excel Discussion (Misc queries) |