Does anyone know how i can format the background colour of
my cells dependent on their content. I can do this in
conditional formatting but only for 3 conditions and i
need about 20.

All i want is code that says

if cell content = fish - cell background = red
if cell content - animal - cell background = blue

etc.

Any one any ideas???

Add a control button then in code use a Case statement

Private Sub CommandButton1_Click()
Dim I As Integer
For I = 1 To 5
Cells(I, 1).Select
Select Case ActiveCell
Case "fish"
ActiveCell.Interior.ColorIndex = 30
Case "animal"
ActiveCell.Interior.ColorIndex = 40
Case Else
ActiveCell.Interior.ColorIndex = 0
End Select
Next

End Sub
-----Original Message-----
Does anyone know how i can format the background colour
of
my cells dependent on their content. I can do this in
conditional formatting but only for 3 conditions and i
need about 20.

All i want is code that says

if cell content = fish - cell background = red
if cell content - animal - cell background = blue

etc.

Any one any ideas???
.

I can't get this code to work properly. I have changed 1
to 5 to 50 so that it covers all my data and have also
coppied the Cells(I, 1).Select to reflect all of my
columns. The problem is is if I change the data and re-run
the macro it does not update the colours it just stays the
smae as the old colours.

Any ideas???

Danny

-----Original Message-----
Add a control button then in code use a Case statement

Private Sub CommandButton1_Click()
Dim I As Integer
For I = 1 To 5
Cells(I, 1).Select
Select Case ActiveCell
Case "fish"
ActiveCell.Interior.ColorIndex = 30
Case "animal"
ActiveCell.Interior.ColorIndex = 40
Case Else
ActiveCell.Interior.ColorIndex = 0
End Select
Next

End Sub
-----Original Message-----
Does anyone know how i can format the background colour
of
my cells dependent on their content. I can do this in
conditional formatting but only for 3 conditions and i
need about 20.

All i want is code that says

if cell content = fish - cell background = red
if cell content - animal - cell background = blue

etc.

Any one any ideas???
.

.

Danny

You should use the Worksheet_Change event, in my opinion. Right click on
the sheet tab and choose View Code. The Paste this into code module.
Change as necessary.

Private Sub Worksheet_Change(ByVal Target As Range)

'Limits the range to which the formatting is applied
If Not Intersect(Target, Me.Range("a1:G10")) Is Nothing Then
Select Case Target.Value
Case "Fish"
Target.Interior.ColorIndex = 6
Case "Animal"
Target.Interior.ColorIndex = 3
'Add more case statements here if you nee them
Case Else
Target.Interior.ColorIndex = -4142
End Select
End If

End Sub

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Danny" wrote in message
...
I can't get this code to work properly. I have changed 1
to 5 to 50 so that it covers all my data and have also
coppied the Cells(I, 1).Select to reflect all of my
columns. The problem is is if I change the data and re-run
the macro it does not update the colours it just stays the
smae as the old colours.

Any ideas???

Danny

-----Original Message-----
Add a control button then in code use a Case statement

Private Sub CommandButton1_Click()
Dim I As Integer
For I = 1 To 5
Cells(I, 1).Select
Select Case ActiveCell
Case "fish"
ActiveCell.Interior.ColorIndex = 30
Case "animal"
ActiveCell.Interior.ColorIndex = 40
Case Else
ActiveCell.Interior.ColorIndex = 0
End Select
Next

End Sub
-----Original Message-----
Does anyone know how i can format the background colour
of
my cells dependent on their content. I can do this in
conditional formatting but only for 3 conditions and i
need about 20.

All i want is code that says

if cell content = fish - cell background = red
if cell content - animal - cell background = blue

etc.

Any one any ideas???
.

.