I've been searching for the last few days with no luck, so I thought I'd
post. Here's my situation:

I have a worksheet set up to track projects. In cell K3 I have the following
formula
=IF(AND(K$2=$G3,K$2<=$I3,$E3="Broiler"),1,IF(AND( K$2=$G3,K$2<=$I3,$E3="Hatchery"),2,IF(AND(K$2=$G 3,K$2<=$I3,$E3="Live
Haul"),3,IF(AND(K$2=$G3,K$2<=$I3,$E3="Feed
Mill"),4,IF(AND(K$2=$G3,K$2<=$I3,$E3="Breeder"),5 ,""))))). This formula is
copied through column IU and row 100.

I need to use conditional formatting to color the cell background and font
of the cells with the formula as follows: green (10) if the formula result is
1, violet (13) if the formula result is 2, red (3) if the formula result is
3, blue (5) if the formula result is 4, orange (46) if the formula result is
5, and white (0) if the formula result is "". I need this to happen if the
user adds or updates a value in column E, G, or I of the corresponding row.

I've tried putting together code from other posts, but I don't understand
what the code is doing well enough to make it work for me.

Any help anyone can provide is very appreciated.
bugsyb6

One of these two Subs should do it for you:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim rng As Range
Dim vRngInput As Range
Set vRngInput = Intersect(Target, Range("A:Z")) '<---------change to suit
If vRngInput Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
For Each rng In vRngInput
'Determine the color
Select Case UCase(rng.Value)
Case Is = "A": Num = 10 'green
Case Is = "B": Num = 1 'black
Case Is = "C": Num = 5 'blue
Case Is = "D": Num = 7 'magenta
Case Is = "E": Num = 46 'orange
Case Is = "F": Num = 3 'red
End Select
'Apply the color
rng.Interior.ColorIndex = Num
Next rng
endit:
Application.EnableEvents = True
End Sub

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 0
Target.Interior.ColorIndex = 5
Case 0.33
Target.Interior.ColorIndex = 10
Case 0.66
Target.Interior.ColorIndex = 6
Case 1
Target.Interior.ColorIndex = 46
End Select
End If
End Sub

This is 'event code'. To use it, right click on your sheet/tab, and paste
it into the window that opens.

HTH,
Ryan---


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"bugsyb6" wrote:

I've been searching for the last few days with no luck, so I thought I'd
post. Here's my situation:

I have a worksheet set up to track projects. In cell K3 I have the following
formula
=IF(AND(K$2=$G3,K$2<=$I3,$E3="Broiler"),1,IF(AND( K$2=$G3,K$2<=$I3,$E3="Hatchery"),2,IF(AND(K$2=$G 3,K$2<=$I3,$E3="Live
Haul"),3,IF(AND(K$2=$G3,K$2<=$I3,$E3="Feed
Mill"),4,IF(AND(K$2=$G3,K$2<=$I3,$E3="Breeder"),5 ,""))))). This formula is
copied through column IU and row 100.

I need to use conditional formatting to color the cell background and font
of the cells with the formula as follows: green (10) if the formula result is
1, violet (13) if the formula result is 2, red (3) if the formula result is
3, blue (5) if the formula result is 4, orange (46) if the formula result is
5, and white (0) if the formula result is "". I need this to happen if the
user adds or updates a value in column E, G, or I of the corresponding row.

I've tried putting together code from other posts, but I don't understand
what the code is doing well enough to make it work for me.

Any help anyone can provide is very appreciated.
bugsyb6

Thanks for the fast reply. I modified the code to fit my range and it works
if I double-click in the formula cell and then hit enter (in essence
re-entering the formula), but it doesn't change the color of the formula cell
if I add or update a value in column E, G, or I of the corresponding row -
which I need it to do.

Any ideas how to get the color to change if the value of the formula changes?
bugsyb6

"ryguy7272" wrote:

One of these two Subs should do it for you:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim rng As Range
Dim vRngInput As Range
Set vRngInput = Intersect(Target, Range("A:Z")) '<---------change to suit
If vRngInput Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
For Each rng In vRngInput
'Determine the color
Select Case UCase(rng.Value)
Case Is = "A": Num = 10 'green
Case Is = "B": Num = 1 'black
Case Is = "C": Num = 5 'blue
Case Is = "D": Num = 7 'magenta
Case Is = "E": Num = 46 'orange
Case Is = "F": Num = 3 'red
End Select
'Apply the color
rng.Interior.ColorIndex = Num
Next rng
endit:
Application.EnableEvents = True
End Sub

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 0
Target.Interior.ColorIndex = 5
Case 0.33
Target.Interior.ColorIndex = 10
Case 0.66
Target.Interior.ColorIndex = 6
Case 1
Target.Interior.ColorIndex = 46
End Select
End If
End Sub

This is 'event code'. To use it, right click on your sheet/tab, and paste
it into the window that opens.

HTH,
Ryan---


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"bugsyb6" wrote:

I've been searching for the last few days with no luck, so I thought I'd
post. Here's my situation:

I have a worksheet set up to track projects. In cell K3 I have the following
formula
=IF(AND(K$2=$G3,K$2<=$I3,$E3="Broiler"),1,IF(AND( K$2=$G3,K$2<=$I3,$E3="Hatchery"),2,IF(AND(K$2=$G 3,K$2<=$I3,$E3="Live
Haul"),3,IF(AND(K$2=$G3,K$2<=$I3,$E3="Feed
Mill"),4,IF(AND(K$2=$G3,K$2<=$I3,$E3="Breeder"),5 ,""))))). This formula is
copied through column IU and row 100.

I need to use conditional formatting to color the cell background and font
of the cells with the formula as follows: green (10) if the formula result is
1, violet (13) if the formula result is 2, red (3) if the formula result is
3, blue (5) if the formula result is 4, orange (46) if the formula result is
5, and white (0) if the formula result is "". I need this to happen if the
user adds or updates a value in column E, G, or I of the corresponding row.

I've tried putting together code from other posts, but I don't understand
what the code is doing well enough to make it work for me.

Any help anyone can provide is very appreciated.
bugsyb6

Hmmm, that doesn't sound right. Are you sure the range in the code covers
the used range (the range you are using on your sheet)?

Tools Options Calculation Automatic

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"bugsyb6" wrote:

Thanks for the fast reply. I modified the code to fit my range and it works
if I double-click in the formula cell and then hit enter (in essence
re-entering the formula), but it doesn't change the color of the formula cell
if I add or update a value in column E, G, or I of the corresponding row -
which I need it to do.

Any ideas how to get the color to change if the value of the formula changes?
bugsyb6

"ryguy7272" wrote:

One of these two Subs should do it for you:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim rng As Range
Dim vRngInput As Range
Set vRngInput = Intersect(Target, Range("A:Z")) '<---------change to suit
If vRngInput Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
For Each rng In vRngInput
'Determine the color
Select Case UCase(rng.Value)
Case Is = "A": Num = 10 'green
Case Is = "B": Num = 1 'black
Case Is = "C": Num = 5 'blue
Case Is = "D": Num = 7 'magenta
Case Is = "E": Num = 46 'orange
Case Is = "F": Num = 3 'red
End Select
'Apply the color
rng.Interior.ColorIndex = Num
Next rng
endit:
Application.EnableEvents = True
End Sub

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 0
Target.Interior.ColorIndex = 5
Case 0.33
Target.Interior.ColorIndex = 10
Case 0.66
Target.Interior.ColorIndex = 6
Case 1
Target.Interior.ColorIndex = 46
End Select
End If
End Sub

This is 'event code'. To use it, right click on your sheet/tab, and paste
it into the window that opens.

HTH,
Ryan---


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"bugsyb6" wrote:

I've been searching for the last few days with no luck, so I thought I'd
post. Here's my situation:

I have a worksheet set up to track projects. In cell K3 I have the following
formula
=IF(AND(K$2=$G3,K$2<=$I3,$E3="Broiler"),1,IF(AND( K$2=$G3,K$2<=$I3,$E3="Hatchery"),2,IF(AND(K$2=$G 3,K$2<=$I3,$E3="Live
Haul"),3,IF(AND(K$2=$G3,K$2<=$I3,$E3="Feed
Mill"),4,IF(AND(K$2=$G3,K$2<=$I3,$E3="Breeder"),5 ,""))))). This formula is
copied through column IU and row 100.

I need to use conditional formatting to color the cell background and font
of the cells with the formula as follows: green (10) if the formula result is
1, violet (13) if the formula result is 2, red (3) if the formula result is
3, blue (5) if the formula result is 4, orange (46) if the formula result is
5, and white (0) if the formula result is "". I need this to happen if the
user adds or updates a value in column E, G, or I of the corresponding row.

I've tried putting together code from other posts, but I don't understand
what the code is doing well enough to make it work for me.

Any help anyone can provide is very appreciated.
bugsyb6

Sorry I didn't get back right away - busy weekend. But yes, the range
includes the cells with the formulas. I've even tried including different
ranges -like A1:IV25- just to see and my formulas update based on what I type
in column E but the color of the formula cell doesn't change. I have
calculation set to Automatic. Any more ideas?

"ryguy7272" wrote:

Hmmm, that doesn't sound right. Are you sure the range in the code covers
the used range (the range you are using on your sheet)?

Tools Options Calculation Automatic

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"bugsyb6" wrote:

Thanks for the fast reply. I modified the code to fit my range and it works
if I double-click in the formula cell and then hit enter (in essence
re-entering the formula), but it doesn't change the color of the formula cell
if I add or update a value in column E, G, or I of the corresponding row -
which I need it to do.

Any ideas how to get the color to change if the value of the formula changes?
bugsyb6

"ryguy7272" wrote:

One of these two Subs should do it for you:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim rng As Range
Dim vRngInput As Range
Set vRngInput = Intersect(Target, Range("A:Z")) '<---------change to suit
If vRngInput Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
For Each rng In vRngInput
'Determine the color
Select Case UCase(rng.Value)
Case Is = "A": Num = 10 'green
Case Is = "B": Num = 1 'black
Case Is = "C": Num = 5 'blue
Case Is = "D": Num = 7 'magenta
Case Is = "E": Num = 46 'orange
Case Is = "F": Num = 3 'red
End Select
'Apply the color
rng.Interior.ColorIndex = Num
Next rng
endit:
Application.EnableEvents = True
End Sub

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 0
Target.Interior.ColorIndex = 5
Case 0.33
Target.Interior.ColorIndex = 10
Case 0.66
Target.Interior.ColorIndex = 6
Case 1
Target.Interior.ColorIndex = 46
End Select
End If
End Sub

This is 'event code'. To use it, right click on your sheet/tab, and paste
it into the window that opens.

HTH,
Ryan---


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"bugsyb6" wrote:

I've been searching for the last few days with no luck, so I thought I'd
post. Here's my situation:

I have a worksheet set up to track projects. In cell K3 I have the following
formula
=IF(AND(K$2=$G3,K$2<=$I3,$E3="Broiler"),1,IF(AND( K$2=$G3,K$2<=$I3,$E3="Hatchery"),2,IF(AND(K$2=$G 3,K$2<=$I3,$E3="Live
Haul"),3,IF(AND(K$2=$G3,K$2<=$I3,$E3="Feed
Mill"),4,IF(AND(K$2=$G3,K$2<=$I3,$E3="Breeder"),5 ,""))))). This formula is
copied through column IU and row 100.

I need to use conditional formatting to color the cell background and font
of the cells with the formula as follows: green (10) if the formula result is
1, violet (13) if the formula result is 2, red (3) if the formula result is
3, blue (5) if the formula result is 4, orange (46) if the formula result is
5, and white (0) if the formula result is "". I need this to happen if the
user adds or updates a value in column E, G, or I of the corresponding row.

I've tried putting together code from other posts, but I don't understand
what the code is doing well enough to make it work for me.

Any help anyone can provide is very appreciated.
bugsyb6

Use a Calculation event instead of change event.

Private Sub Worksheet_Calculate()
stuff to do
End Sub


Gord Dibben MS Excel MVP

On Mon, 23 Mar 2009 06:10:01 -0700, bugsyb6
wrote:

Sorry I didn't get back right away - busy weekend. But yes, the range
includes the cells with the formulas. I've even tried including different
ranges -like A1:IV25- just to see and my formulas update based on what I type
in column E but the color of the formula cell doesn't change. I have
calculation set to Automatic. Any more ideas?

"ryguy7272" wrote:

Hmmm, that doesn't sound right. Are you sure the range in the code covers
the used range (the range you are using on your sheet)?

Tools Options Calculation Automatic

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"bugsyb6" wrote:

Thanks for the fast reply. I modified the code to fit my range and it works
if I double-click in the formula cell and then hit enter (in essence
re-entering the formula), but it doesn't change the color of the formula cell
if I add or update a value in column E, G, or I of the corresponding row -
which I need it to do.

Any ideas how to get the color to change if the value of the formula changes?
bugsyb6

"ryguy7272" wrote:

One of these two Subs should do it for you:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim rng As Range
Dim vRngInput As Range
Set vRngInput = Intersect(Target, Range("A:Z")) '<---------change to suit
If vRngInput Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
For Each rng In vRngInput
'Determine the color
Select Case UCase(rng.Value)
Case Is = "A": Num = 10 'green
Case Is = "B": Num = 1 'black
Case Is = "C": Num = 5 'blue
Case Is = "D": Num = 7 'magenta
Case Is = "E": Num = 46 'orange
Case Is = "F": Num = 3 'red
End Select
'Apply the color
rng.Interior.ColorIndex = Num
Next rng
endit:
Application.EnableEvents = True
End Sub

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 0
Target.Interior.ColorIndex = 5
Case 0.33
Target.Interior.ColorIndex = 10
Case 0.66
Target.Interior.ColorIndex = 6
Case 1
Target.Interior.ColorIndex = 46
End Select
End If
End Sub

This is 'event code'. To use it, right click on your sheet/tab, and paste
it into the window that opens.

HTH,
Ryan---


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"bugsyb6" wrote:

I've been searching for the last few days with no luck, so I thought I'd
post. Here's my situation:

I have a worksheet set up to track projects. In cell K3 I have the following
formula
=IF(AND(K$2=$G3,K$2<=$I3,$E3="Broiler"),1,IF(AND( K$2=$G3,K$2<=$I3,$E3="Hatchery"),2,IF(AND(K$2=$G 3,K$2<=$I3,$E3="Live
Haul"),3,IF(AND(K$2=$G3,K$2<=$I3,$E3="Feed
Mill"),4,IF(AND(K$2=$G3,K$2<=$I3,$E3="Breeder"),5 ,""))))). This formula is
copied through column IU and row 100.

I need to use conditional formatting to color the cell background and font
of the cells with the formula as follows: green (10) if the formula result is
1, violet (13) if the formula result is 2, red (3) if the formula result is
3, blue (5) if the formula result is 4, orange (46) if the formula result is
5, and white (0) if the formula result is "". I need this to happen if the
user adds or updates a value in column E, G, or I of the corresponding row.

I've tried putting together code from other posts, but I don't understand
what the code is doing well enough to make it work for me.

Any help anyone can provide is very appreciated.
bugsyb6

Hello,

Do you know that with Excel 2007 you can apply more than 3 conditional
formats?

Regards,
Bernd

Bernd -
Thanks for the info but, unfortunately, my company is still using Excel 2003
with no word on when they are upgrading.

bugsyb