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>=$G3,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>=$G3,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>=$G3,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>=$G3,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>=$G3,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
<(E-Mail Removed)> 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>=$G3,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

When I put the code Ryan gave me into a Calculation event, I get an error for
the Target. Do I need to declare Target As something, or does the code need
to be modified to not include the Target? Sorry, I'm just not very good at
VBA yet (but I am learning).

p.s. if this posts twice I apologize - it's giving me issues!

"Gord Dibben" wrote:

> 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
> <(E-Mail Removed)> 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>=$G3,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
>
>

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

bugsyb

I played around and got it to work. I changed the numbers to letters in my
formula and here's what I ended up with.

Private Sub Worksheet_Calculate()
Dim WatchRange As Range
Dim CellVal As String
Set WatchRange = Range("K3:IU100")

For Each Cell In WatchRange
If Cell.Value = "A" Then
Cell.Interior.ColorIndex = 10
Cell.Font.ColorIndex = 10
ElseIf Cell.Value = "B" Then
Cell.Interior.ColorIndex = 13
Cell.Font.ColorIndex = 13
ElseIf Cell.Value = "C" Then
Cell.Interior.ColorIndex = 3
Cell.Font.ColorIndex = 3
ElseIf Cell.Value = "D" Then
Cell.Interior.ColorIndex = 5
Cell.Font.ColorIndex = 5
ElseIf Cell.Value = "E" Then
Cell.Interior.ColorIndex = 6
Cell.Font.ColorIndex = 6
ElseIf Cell.Value = "" Then
Cell.Interior.ColorIndex = 0
Cell.Font.ColorIndex = 0
End If
Next Cell
End Sub

It may not be the best looking code ever, but it does the job! Thanks for
all of the help.

"bugsyb" wrote:

> When I put the code Ryan gave me into a Calculation event, I get an error for
> the Target. Do I need to declare Target As something, or does the code need
> to be modified to not include the Target? Sorry, I'm just not very good at
> VBA yet (but I am learning).
>
> p.s. if this posts twice I apologize - it's giving me issues!
>
> "Gord Dibben" wrote:
>
> > 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
> > <(E-Mail Removed)> 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>=$G3,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
> >
> >