Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code for bacground colors
I tried using this code, in my Excel spreadsheet, for my worksheet but it is
not working. Could it be that the cells I'm trying to color are merged? Any help would be appreciated. Thank you. Private Sub Worksheet_Change(ByVal Target As Range) Dim WatchRange As Range Dim CellVal As String If Target.Cells.Count 1 Then Exit Sub If Target = "" Then Exit Sub CellVal = Target Set WatchRange = Range("A1:c100") 'change to suit If Not Intersect(Target, WatchRange) Is Nothing Then Select Case CellVal Case "Dog" Target.Interior.ColorIndex = 5 Case "Cat" Target.Interior.ColorIndex = 10 Case "Other" Target.Interior.ColorIndex = 6 Case "Rabbit" Target.Interior.ColorIndex = 46 Case "Goat" Target.Interior.ColorIndex = 45 End Select End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code for bacground colors
I'm also using a vlookup in that cell.
"Antney" wrote: I tried using this code, in my Excel spreadsheet, for my worksheet but it is not working. Could it be that the cells I'm trying to color are merged? Any help would be appreciated. Thank you. Private Sub Worksheet_Change(ByVal Target As Range) Dim WatchRange As Range Dim CellVal As String If Target.Cells.Count 1 Then Exit Sub If Target = "" Then Exit Sub CellVal = Target Set WatchRange = Range("A1:c100") 'change to suit If Not Intersect(Target, WatchRange) Is Nothing Then Select Case CellVal Case "Dog" Target.Interior.ColorIndex = 5 Case "Cat" Target.Interior.ColorIndex = 10 Case "Other" Target.Interior.ColorIndex = 6 Case "Rabbit" Target.Interior.ColorIndex = 46 Case "Goat" Target.Interior.ColorIndex = 45 End Select End If End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code for bacground colors
That's your problem. If you want to do it with code, you need to look at
the precedents that are changed, not the resulting values. Wouldn't it be easier just to add some conditional formats on A1:C100 to address the colorindex? "Antney" wrote: I'm also using a vlookup in that cell. "Antney" wrote: I tried using this code, in my Excel spreadsheet, for my worksheet but it is not working. Could it be that the cells I'm trying to color are merged? Any help would be appreciated. Thank you. Private Sub Worksheet_Change(ByVal Target As Range) Dim WatchRange As Range Dim CellVal As String If Target.Cells.Count 1 Then Exit Sub If Target = "" Then Exit Sub CellVal = Target Set WatchRange = Range("A1:c100") 'change to suit If Not Intersect(Target, WatchRange) Is Nothing Then Select Case CellVal Case "Dog" Target.Interior.ColorIndex = 5 Case "Cat" Target.Interior.ColorIndex = 10 Case "Other" Target.Interior.ColorIndex = 6 Case "Rabbit" Target.Interior.ColorIndex = 46 Case "Goat" Target.Interior.ColorIndex = 45 End Select End If End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code for bacground color
Sub FormatRange(YourRangeNameHere As Range)
'This macro shades cells based on their contents. Essentially it is conditional formatting with more than 'three kinds of conditions. To add a condition select and copy one of the three lines starting with '"ElseIf cell.Value". Paste what you have copied at the end of the list and then alter the value, 'currently shown in "" and change the colour value. Pick a colour from the handy list below. 'excel fill colours and their index numbers '1-black | 53-brown | 52-olive green | 51-dark green | 49-dark teal | 25-dark blue | 55-indigo | 56-80% grey '9-dark red | 46-orange | 12-dark yellow | 10-green | 14-teal | 5-blue | 47-blue-grey | 16-50% grey '3-red | 45-lgt orange | 43-lime | 50-sea green | 42-aqua | 41-light blue | 29-violet | 48-40% grey '7-pink | 44-gold | 6-yellow | 4-bright green | 8-turquoise | 33-sky blue | 54-plum | 15-25% grey '38-rose | 40-tan | 36-lgt yellow | 35-lgt green | 34-lgt turqoise | 37-pale blue | 39-lavender | 2-white '0-no fill For Each cell In YourRangeNameHere If cell.Value = "" Then cell.Interior.ColorIndex = 0 'no fill ElseIf cell.Value = "0.25" Then cell.Interior.ColorIndex = 6 'Yellow 'cell.Font.ColorIndex = 6 'Yellow ElseIf cell.Value = "0.5" Then cell.Interior.ColorIndex = 44 'Gold 'cell.Font.ColorIndex = 44 'Gold ElseIf cell.Value = "0.75" Then cell.Interior.ColorIndex = 45 'lgt orange 'cell.Font.ColorIndex = 45 'lgt orange ElseIf cell.Value = "1" Then cell.Interior.ColorIndex = 46 'Orange 'cell.Font.ColorIndex = 46 'Orange End If Next cell End Sub Barb Reinhardt wrote: That's your problem. 19-Oct-09 That's your problem. If you want to do it with code, you need to look at the precedents that are changed, not the resulting values. Wouldn't it be easier just to add some conditional formats on A1:C100 to address the colorindex? "Antney" wrote: Previous Posts In This Thread: EggHeadCafe - Software Developer Portal of Choice Silverlight / WPF Architecture MVP Summit Presentation http://www.eggheadcafe.com/tutorials...f-archite.aspx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA Code Colors | Excel Programming | |||
changing bacground colour if formula altered | Excel Programming | |||
Print VBA code in Editor format colors | Excel Programming | |||
VBA code to count colors/shapes? | Excel Programming | |||
how can i select all the cells with same color on a sheet if there are multipale colors by vba code | Charts and Charting in Excel |