How do I change this code to first apply the "If" to data in the cell across
in Column E of the current row (eg RC5), if it's true and the value in the
current cell is greater than 0 then apply the formatting?
Thanks in Advance
BeSmart

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Value = "Cat" Then
Target.Cells.Interior.ColorIndex = 1
ElseIf Target.Value = "Dog" Then
Target.Cells.Interior.ColorIndex = 4
ElseIf Target.Value = "Horse" Then
Target.Cells.Interior.ColorIndex = 5
ElseIf Target.Value = "Camel" Then
Target.Cells.Interior.ColorIndex = 6
ElseIf Target.Value = "Pig" Then
Target.Cells.Interior.ColorIndex = 7
Else
Target.Cells.Interior.ColorIndex = xlnone
End If

End Sub

On Sun, 12 Sep 2004 15:07:01 -0700, "BeSmart"
wrote:

How do I change this code to first apply the "If" to data in the cell across
in Column E of the current row (eg RC5), if it's true and the value in the
current cell is greater than 0 then apply the formatting?
Thanks in Advance
BeSmart

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Value = "Cat" Then
Target.Cells.Interior.ColorIndex = 1
ElseIf Target.Value = "Dog" Then
Target.Cells.Interior.ColorIndex = 4
ElseIf Target.Value = "Horse" Then
Target.Cells.Interior.ColorIndex = 5
ElseIf Target.Value = "Camel" Then
Target.Cells.Interior.ColorIndex = 6
ElseIf Target.Value = "Pig" Then
Target.Cells.Interior.ColorIndex = 7
Else
Target.Cells.Interior.ColorIndex = xlnone
End If

End Sub

What do you mean by "current cell"? Because if it's Target, and Target
contains a string, you will get a type mismatch error testing it to see if it's
greater than 0.

You can certainly test the cell in Column E of the target:

=========================
If Cells(Target.Row, 5) = True Then
Select Case Target.Text
Case Is = "Cat"
Target.Cells.Interior.ColorIndex = 1
Case Is = "Dog"
Target.Cells.Interior.ColorIndex = 4
Case Is = "Horse"
Target.Cells.Interior.ColorIndex = 5
Case Is = "Camel"
Target.Cells.Interior.ColorIndex = 6
Case Is = "Pig"
Target.Cells.Interior.ColorIndex = 7
Case Else
Target.Cells.Interior.ColorIndex = xlNone
End Select
End If
========================


--ron

Hi Ron
By current cell I mean the conditional formatting has to apply to the cell
that the user just entered a number into, but the interior colour depends on
the category (eg Pig, Cat etc) that they entered in column E for the same
row...
Hope that's clearer...

"Ron Rosenfeld" wrote:

On Sun, 12 Sep 2004 15:07:01 -0700, "BeSmart"
wrote:

How do I change this code to first apply the "If" to data in the cell across
in Column E of the current row (eg RC5), if it's true and the value in the
current cell is greater than 0 then apply the formatting?
Thanks in Advance
BeSmart

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Value = "Cat" Then
Target.Cells.Interior.ColorIndex = 1
ElseIf Target.Value = "Dog" Then
Target.Cells.Interior.ColorIndex = 4
ElseIf Target.Value = "Horse" Then
Target.Cells.Interior.ColorIndex = 5
ElseIf Target.Value = "Camel" Then
Target.Cells.Interior.ColorIndex = 6
ElseIf Target.Value = "Pig" Then
Target.Cells.Interior.ColorIndex = 7
Else
Target.Cells.Interior.ColorIndex = xlnone
End If

End Sub

What do you mean by "current cell"? Because if it's Target, and Target
contains a string, you will get a type mismatch error testing it to see if it's
greater than 0.

You can certainly test the cell in Column E of the target:

=========================
If Cells(Target.Row, 5) = True Then
Select Case Target.Text
Case Is = "Cat"
Target.Cells.Interior.ColorIndex = 1
Case Is = "Dog"
Target.Cells.Interior.ColorIndex = 4
Case Is = "Horse"
Target.Cells.Interior.ColorIndex = 5
Case Is = "Camel"
Target.Cells.Interior.ColorIndex = 6
Case Is = "Pig"
Target.Cells.Interior.ColorIndex = 7
Case Else
Target.Cells.Interior.ColorIndex = xlNone
End Select
End If
========================


--ron

On Sun, 12 Sep 2004 16:37:01 -0700, "BeSmart"
wrote:

Hi Ron
By current cell I mean the conditional formatting has to apply to the cell
that the user just entered a number into, but the interior colour depends on
the category (eg Pig, Cat etc) that they entered in column E for the same
row...
Hope that's clearer...


If I understand you correctly, you want Target to be 0, and also for one of
the specified animals to be in the same row as Target but in Column E.

Try this:

==================
For Each c In Target
If IsNumeric(Target.Value) Then
If Target.Value 0 Then
Select Case Cells(Target.Row, 5).Text
Case Is = "Cat"
Target.Cells.Interior.ColorIndex = 1
Case Is = "Dog"
Target.Cells.Interior.ColorIndex = 4
Case Is = "Horse"
Target.Cells.Interior.ColorIndex = 5
Case Is = "Camel"
Target.Cells.Interior.ColorIndex = 6
Case Is = "Pig"
Target.Cells.Interior.ColorIndex = 7
Case Else
Target.Cells.Interior.ColorIndex = xlNone
End Select
End If
End If
Next c
=========================

However, unless you want the formatting to "stick" once you've made an entry,
you should probably set colorindex=xln=None at the beginning.

Otherwise, if you type in a number, and it gets formatted, deleting that number
will not reset the format to xlNone.

Since you might have other cells in the worksheet that are formatted, you might
want to test for the location of Target, also, and only go through the process
of resetting to xlNone if Target is in the range where you might be typing in
numbers.


--ron

Thanks Ron - that worked really well - but you're right, the user will
delete numbers from cells at which time I need the formatting to revert to
none.

I tried inserting the "set colorindex=xln=None" at the beginning as you said
but I got error messages. Obviously I put it in the wrong spot - please show
me where?

==============================================
Private Sub Worksheet_Change(ByVal Target As Range)

For Each c In Target
If IsNumeric(Target.Value) Then
If Target.Value 0 Then
Select Case Cells(Target.Row, 5).Text
Case Is = "Cat"
Target.Cells.Interior.ColorIndex = 1
Case Is = "Dog"
Target.Cells.Interior.ColorIndex = 4
Case Is = "Horse"
Target.Cells.Interior.ColorIndex = 5
Case Is = "Camel"
Target.Cells.Interior.ColorIndex = 6
Case Is = "Pig"
Target.Cells.Interior.ColorIndex = 7
Case Else
Target.Cells.Interior.ColorIndex = xlNone
End Select
End If
End If
Next c
End Sub
===============================================

Also, I might be wrong here but if users won't need to enter numbers
anywhere except where the conditional formatting is required, restricting a
range shouldn't be necessary right?
But if you can explain how I write that into the code it might be very
userful in the future. Note, There are two ranges on my spreadsheet:
E14:E605 and M14:GR605.

Thank you SOOOO much for your help!!! It's been extremely helpful.
BeSmart

On Sun, 12 Sep 2004 22:09:02 -0700, "BeSmart"
wrote:


Thanks Ron - that worked really well - but you're right, the user will
delete numbers from cells at which time I need the formatting to revert to
none.

I tried inserting the "set colorindex=xln=None" at the beginning as you said
but I got error messages. Obviously I put it in the wrong spot - please show
me where?

==============================================
Private Sub Worksheet_Change(ByVal Target As Range)

For Each c In Target
If IsNumeric(Target.Value) Then
If Target.Value 0 Then
Select Case Cells(Target.Row, 5).Text
Case Is = "Cat"
Target.Cells.Interior.ColorIndex = 1
Case Is = "Dog"
Target.Cells.Interior.ColorIndex = 4
Case Is = "Horse"
Target.Cells.Interior.ColorIndex = 5
Case Is = "Camel"
Target.Cells.Interior.ColorIndex = 6
Case Is = "Pig"
Target.Cells.Interior.ColorIndex = 7
Case Else
Target.Cells.Interior.ColorIndex = xlNone
End Select
End If
End If
Next c
End Sub
===============================================

Also, I might be wrong here but if users won't need to enter numbers
anywhere except where the conditional formatting is required, restricting a
range shouldn't be necessary right?

So long as you can be certain that they will not enter a number elsewhere
(perhaps by accident). You could do that with protection.

But if you can explain how I write that into the code it might be very
userful in the future. Note, There are two ranges on my spreadsheet:
E14:E605 and M14:GR605.

Thank you SOOOO much for your help!!! It's been extremely helpful.
BeSmart

I'm guessing the user entry area is M14:GR605?

So you could have something like this:

=======================
For Each c In Target
If Not Intersect(c, [M1:GR605]) Is Nothing Then
Target.Cells.Interior.ColorIndex = xlNone
If IsNumeric(Target.Value) Then
If Target.Value 0 Then
Select Case LCase(Cells(Target.Row, 5).Text)
Case Is = "cat"
Target.Cells.Interior.ColorIndex = 1
Case Is = "dog"
Target.Cells.Interior.ColorIndex = 4
Case Is = "horse"
Target.Cells.Interior.ColorIndex = 5
Case Is = "camel"
Target.Cells.Interior.ColorIndex = 6
Case Is = "pig"
Target.Cells.Interior.ColorIndex = 7

End Select
End If
End If
End If
Next c
================================

Note that I also made the animal name case independent. If this is not
appropriate, you should remove the LCase function and capitalize the animal
names.


--ron

Thanks Ron - works a treat!!!!!
appreciate your time - I've definitely learn from your advice.
BeSmart

On Mon, 13 Sep 2004 14:29:02 -0700, "BeSmart"
wrote:

Thanks Ron - works a treat!!!!!
appreciate your time - I've definitely learn from your advice.
BeSmart


You're welcome. Thank you for the feedback. Glad to help. I learn, too.


--ron