VBA Code for bacground colors

[Antney]

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

 

[Antney]

I'm also using a vlookup in that cell.

 

[Barb Reinhardt]

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?

 

[Aaron Miller]

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 a
the precedents that are changed, not the resulting values

Wouldn't it be easier just to add some conditional formats on A1:C100 t
address the colorindex


:

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
Silverlight / WPF Architecture MVP Summit Presentation
http://www.eggheadcafe.com/tutorial...cc-e5d56e550430/silverlight--wpf-archite.aspx