Hi,

I have two columns in my worksheet which are used as lookup input
parameters. A results column is populated with the value returned from this
look up. The cells of the result column will change color based on the value
returned from the lookup. I have used the code shown below to acheive this.
This works perfectly when the look up is performed for the first time.
However, if a user changes one of the parameters in the worksheet, the lookup
will be performed - the correct value is returned to the results column but
the color does not change. Does anyone know how I can get the color to change
dynamically - i'm sure its some flaw in my code.

Thanks a lot guys

BC

Conditional Formatting Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer

If Not Intersect(Target, Range("Table")) Is Nothing Then
Select Case Target(1, 1)
Case 1 To 5
icolor = 5
Case 6 To 10
icolor = 6
Case 11 To 15
icolor = 46
Case 16 To 20
icolor = 3
Case 21 To 25
icolor = 13
Case Else
'Whatever
End Select

Target.Interior.ColorIndex = icolor
End If

End Sub

The code works fine.

To make it work, you first need to create a named range of cells where this
conditional formatting will occur, with the name "table".

To do this, select some cells on your worksheet (eg A1:E7).
Now have a look on the top left there is the name box which should probably
say A1 in it. Click in there and overwrite A1 with the word table.

Now try entering different values in and outside A1:E7.

And then play with what you can do under Insert > Name > Define
--
Allllen


"BC" wrote:

> Hi,
>
> I have two columns in my worksheet which are used as lookup input
> parameters. A results column is populated with the value returned from this
> look up. The cells of the result column will change color based on the value
> returned from the lookup. I have used the code shown below to acheive this.
> This works perfectly when the look up is performed for the first time.
> However, if a user changes one of the parameters in the worksheet, the lookup
> will be performed - the correct value is returned to the results column but
> the color does not change. Does anyone know how I can get the color to change
> dynamically - i'm sure its some flaw in my code.
>
> Thanks a lot guys
>
> BC
>
> Conditional Formatting Code:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim icolor As Integer
>
> If Not Intersect(Target, Range("Table")) Is Nothing Then
> Select Case Target(1, 1)
> Case 1 To 5
> icolor = 5
> Case 6 To 10
> icolor = 6
> Case 11 To 15
> icolor = 46
> Case 16 To 20
> icolor = 3
> Case 21 To 25
> icolor = 13
> Case Else
> 'Whatever
> End Select
>
> Target.Interior.ColorIndex = icolor
> End If
>
> End Sub
>
>
>
>

I am assuming that you want to change the colour when the value of the
formula changes. In order to do that try checking the dependants of the cell
that is being changed... The the dependant is the formula in your range then
chenge the colour of the dependant cell. Something like this...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer

If Not Intersect(Target.Dependents, Range("Table")) Is Nothing Then
Select Case Target.Dependents(1, 1)
Case 1 To 5
icolor = 5
Case 6 To 10
icolor = 6
Case 11 To 15
icolor = 46
Case 16 To 20
icolor = 3
Case 21 To 25
icolor = 13
Case Else
'Whatever
End Select

Target.Dependents.Interior.ColorIndex = icolor
End If
End Sub

--
HTH...

Jim Thomlinson


"BC" wrote:

> Hi,
>
> I have two columns in my worksheet which are used as lookup input
> parameters. A results column is populated with the value returned from this
> look up. The cells of the result column will change color based on the value
> returned from the lookup. I have used the code shown below to acheive this.
> This works perfectly when the look up is performed for the first time.
> However, if a user changes one of the parameters in the worksheet, the lookup
> will be performed - the correct value is returned to the results column but
> the color does not change. Does anyone know how I can get the color to change
> dynamically - i'm sure its some flaw in my code.
>
> Thanks a lot guys
>
> BC
>
> Conditional Formatting Code:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim icolor As Integer
>
> If Not Intersect(Target, Range("Table")) Is Nothing Then
> Select Case Target(1, 1)
> Case 1 To 5
> icolor = 5
> Case 6 To 10
> icolor = 6
> Case 11 To 15
> icolor = 46
> Case 16 To 20
> icolor = 3
> Case 21 To 25
> icolor = 13
> Case Else
> 'Whatever
> End Select
>
> Target.Interior.ColorIndex = icolor
> End If
>
> End Sub
>
>
>
>

Hi Allllen, thanks for the reply.

The code does work if you are manually inputting the values. But if the
value is a product of a lookup, then it only works the first time. If the
lookup is performed again, the value in the conditionally formatted cell will
change but the background color will not.

Any ideas? Really appreciate the help,

Thanks

"Allllen" wrote:

> The code works fine.
>
> To make it work, you first need to create a named range of cells where this
> conditional formatting will occur, with the name "table".
>
> To do this, select some cells on your worksheet (eg A1:E7).
> Now have a look on the top left there is the name box which should probably
> say A1 in it. Click in there and overwrite A1 with the word table.
>
> Now try entering different values in and outside A1:E7.
>
> And then play with what you can do under Insert > Name > Define
> --
> Allllen
>
>
> "BC" wrote:
>
> > Hi,
> >
> > I have two columns in my worksheet which are used as lookup input
> > parameters. A results column is populated with the value returned from this
> > look up. The cells of the result column will change color based on the value
> > returned from the lookup. I have used the code shown below to acheive this.
> > This works perfectly when the look up is performed for the first time.
> > However, if a user changes one of the parameters in the worksheet, the lookup
> > will be performed - the correct value is returned to the results column but
> > the color does not change. Does anyone know how I can get the color to change
> > dynamically - i'm sure its some flaw in my code.
> >
> > Thanks a lot guys
> >
> > BC
> >
> > Conditional Formatting Code:
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > Dim icolor As Integer
> >
> > If Not Intersect(Target, Range("Table")) Is Nothing Then
> > Select Case Target(1, 1)
> > Case 1 To 5
> > icolor = 5
> > Case 6 To 10
> > icolor = 6
> > Case 11 To 15
> > icolor = 46
> > Case 16 To 20
> > icolor = 3
> > Case 21 To 25
> > icolor = 13
> > Case Else
> > 'Whatever
> > End Select
> >
> > Target.Interior.ColorIndex = icolor
> > End If
> >
> > End Sub
> >
> >
> >
> >

Sorry that code I posted needs to be a little more fool proof... Try this...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
Dim rng As Range
On Error Resume Next
Set rng = Intersect(Target.Dependents, Range("Table"))
On Error GoTo 0
If rng Is Nothing Then Exit Sub

Select Case rng(1, 1)
Case 1 To 5
icolor = 5
Case 6 To 10
icolor = 6
Case 11 To 15
icolor = 46
Case 16 To 20
icolor = 3
Case 21 To 25
icolor = 13
Case Else
'Whatever
End Select

rng.Interior.ColorIndex = icolor

End Sub
--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

> I am assuming that you want to change the colour when the value of the
> formula changes. In order to do that try checking the dependants of the cell
> that is being changed... The the dependant is the formula in your range then
> chenge the colour of the dependant cell. Something like this...
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim icolor As Integer
>
> If Not Intersect(Target.Dependents, Range("Table")) Is Nothing Then
> Select Case Target.Dependents(1, 1)
> Case 1 To 5
> icolor = 5
> Case 6 To 10
> icolor = 6
> Case 11 To 15
> icolor = 46
> Case 16 To 20
> icolor = 3
> Case 21 To 25
> icolor = 13
> Case Else
> 'Whatever
> End Select
>
> Target.Dependents.Interior.ColorIndex = icolor
> End If
> End Sub
>
> --
> HTH...
>
> Jim Thomlinson
>
>
> "BC" wrote:
>
> > Hi,
> >
> > I have two columns in my worksheet which are used as lookup input
> > parameters. A results column is populated with the value returned from this
> > look up. The cells of the result column will change color based on the value
> > returned from the lookup. I have used the code shown below to acheive this.
> > This works perfectly when the look up is performed for the first time.
> > However, if a user changes one of the parameters in the worksheet, the lookup
> > will be performed - the correct value is returned to the results column but
> > the color does not change. Does anyone know how I can get the color to change
> > dynamically - i'm sure its some flaw in my code.
> >
> > Thanks a lot guys
> >
> > BC
> >
> > Conditional Formatting Code:
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > Dim icolor As Integer
> >
> > If Not Intersect(Target, Range("Table")) Is Nothing Then
> > Select Case Target(1, 1)
> > Case 1 To 5
> > icolor = 5
> > Case 6 To 10
> > icolor = 6
> > Case 11 To 15
> > icolor = 46
> > Case 16 To 20
> > icolor = 3
> > Case 21 To 25
> > icolor = 13
> > Case Else
> > 'Whatever
> > End Select
> >
> > Target.Interior.ColorIndex = icolor
> > End If
> >
> > End Sub
> >
> >
> >
> >