Worksheet change code to colour in rows in a database

Good afternoon, all,

One column in my database ("ColStatus") is validated by a drop down list,
and depending on the value selected, I want the interior colour for all
database cells in the target row to change. In the code shown below, the
msgboxes work fine, so the case logic is OK, but in each case, the colour
doesn't change.
Can any clever person out there suggest why this might be?
The worksheet isn't protected.
Thanks in anticipation

Pete



Private Sub Worksheet_Change(ByVal Target As Range)

Dim DBSheet As Worksheet
Dim ColStatus As Range

Set DBSheet = Sheets("Database")
Set ColStatus = DBSheet.Range("ColStatus")

Set Intersection = Intersect(Target, ColStatus)
If Not Intersection Is Nothing Then
Select Case Target.Formula
Case "Withdrawn"
MsgBox ("Withdrawn")
Target.Offset(0, -12).Resize(1, 30).Interior.ColorIndex = 3
Target.Offset(0, -12).Resize(1, 30).Font.ColorIndex =
xlAutomatic
Case "Completed"
MsgBox ("Completed")
Target.Offset(0, -12).Resize(1, 30).Interior.ColorIndex = 4
Target.Offset(0, -12).Resize(1, 30).Font.ColorIndex =
xlAutomatic
Case "On Hold"
MsgBox ("On Hold")
Target.Offset(0, -12).Resize(1, 30).Interior.ColorIndex = 45
Target.Offset(0, -12).Resize(1, 30).Font.ColorIndex =
xlAutomatic
Case Else
MsgBox ("Other")
Target.Offset(0, -12).Resize(1, 30).Interior.ColorIndex = 38
Target.Offset(0, -12).Resize(1, 30).Font.ColorIndex =
xlAutomatic
End Select
End If
End Sub

Are you sure that

Target.Offset(0, -12)

is valid, that is that the change column has at least 12 columns left?

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Peter Rooney" <PeterRooney@discussions.microsoft.com> wrote in message
news:C56CBB92-0659-48CB-BE76-4199969830BD@microsoft.com...
> Good afternoon, all,
>
> One column in my database ("ColStatus") is validated by a drop down list,
> and depending on the value selected, I want the interior colour for all
> database cells in the target row to change. In the code shown below, the
> msgboxes work fine, so the case logic is OK, but in each case, the colour
> doesn't change.
> Can any clever person out there suggest why this might be?
> The worksheet isn't protected.
> Thanks in anticipation
>
> Pete
>
>
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Dim DBSheet As Worksheet
> Dim ColStatus As Range
>
> Set DBSheet = Sheets("Database")
> Set ColStatus = DBSheet.Range("ColStatus")
>
> Set Intersection = Intersect(Target, ColStatus)
> If Not Intersection Is Nothing Then
> Select Case Target.Formula
> Case "Withdrawn"
> MsgBox ("Withdrawn")
> Target.Offset(0, -12).Resize(1, 30).Interior.ColorIndex =
3
> Target.Offset(0, -12).Resize(1, 30).Font.ColorIndex =
> xlAutomatic
> Case "Completed"
> MsgBox ("Completed")
> Target.Offset(0, -12).Resize(1, 30).Interior.ColorIndex =
4
> Target.Offset(0, -12).Resize(1, 30).Font.ColorIndex =
> xlAutomatic
> Case "On Hold"
> MsgBox ("On Hold")
> Target.Offset(0, -12).Resize(1, 30).Interior.ColorIndex =
45
> Target.Offset(0, -12).Resize(1, 30).Font.ColorIndex =
> xlAutomatic
> Case Else
> MsgBox ("Other")
> Target.Offset(0, -12).Resize(1, 30).Interior.ColorIndex =
38
> Target.Offset(0, -12).Resize(1, 30).Font.ColorIndex =
> xlAutomatic
> End Select
> End If
> End Sub
>

Bob,

In answer to your question, Yes, BUT - an update:

If I take the validation off a cell in this column and just type in
something like "On Hold", then the colouring works.
If the cell becomes "On Hold" as the result of selecting that value from a
dropdown list i.e. using data validation, then it doesn't.

Does the worksheet change event work differently if a cell's value is
selected from a list, as against being entered directly?

Thanks

Pete



"Bob Phillips" wrote:

> Are you sure that
>
> Target.Offset(0, -12)
>
> is valid, that is that the change column has at least 12 columns left?
>
> --
> HTH
>
> Bob Phillips
>
> (remove xxx from email address if mailing direct)
>
> "Peter Rooney" <PeterRooney@discussions.microsoft.com> wrote in message
> news:C56CBB92-0659-48CB-BE76-4199969830BD@microsoft.com...
> > Good afternoon, all,
> >
> > One column in my database ("ColStatus") is validated by a drop down list,
> > and depending on the value selected, I want the interior colour for all
> > database cells in the target row to change. In the code shown below, the
> > msgboxes work fine, so the case logic is OK, but in each case, the colour
> > doesn't change.
> > Can any clever person out there suggest why this might be?
> > The worksheet isn't protected.
> > Thanks in anticipation
> >
> > Pete
> >
> >
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> >
> > Dim DBSheet As Worksheet
> > Dim ColStatus As Range
> >
> > Set DBSheet = Sheets("Database")
> > Set ColStatus = DBSheet.Range("ColStatus")
> >
> > Set Intersection = Intersect(Target, ColStatus)
> > If Not Intersection Is Nothing Then
> > Select Case Target.Formula
> > Case "Withdrawn"
> > MsgBox ("Withdrawn")
> > Target.Offset(0, -12).Resize(1, 30).Interior.ColorIndex =
> 3
> > Target.Offset(0, -12).Resize(1, 30).Font.ColorIndex =
> > xlAutomatic
> > Case "Completed"
> > MsgBox ("Completed")
> > Target.Offset(0, -12).Resize(1, 30).Interior.ColorIndex =
> 4
> > Target.Offset(0, -12).Resize(1, 30).Font.ColorIndex =
> > xlAutomatic
> > Case "On Hold"
> > MsgBox ("On Hold")
> > Target.Offset(0, -12).Resize(1, 30).Interior.ColorIndex =
> 45
> > Target.Offset(0, -12).Resize(1, 30).Font.ColorIndex =
> > xlAutomatic
> > Case Else
> > MsgBox ("Other")
> > Target.Offset(0, -12).Resize(1, 30).Interior.ColorIndex =
> 38
> > Target.Offset(0, -12).Resize(1, 30).Font.ColorIndex =
> > xlAutomatic
> > End Select
> > End If
> > End Sub
> >
>
>
>

Excel 97?

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Peter Rooney" <PeterRooney@discussions.microsoft.com> wrote in message
news:2C9EE6E2-F33C-4695-8B16-24AC6E99774A@microsoft.com...
> Bob,
>
> In answer to your question, Yes, BUT - an update:
>
> If I take the validation off a cell in this column and just type in
> something like "On Hold", then the colouring works.
> If the cell becomes "On Hold" as the result of selecting that value from a
> dropdown list i.e. using data validation, then it doesn't.
>
> Does the worksheet change event work differently if a cell's value is
> selected from a list, as against being entered directly?
>
> Thanks
>
> Pete
>
>
>
> "Bob Phillips" wrote:
>
> > Are you sure that
> >
> > Target.Offset(0, -12)
> >
> > is valid, that is that the change column has at least 12 columns left?
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove xxx from email address if mailing direct)
> >
> > "Peter Rooney" <PeterRooney@discussions.microsoft.com> wrote in message
> > news:C56CBB92-0659-48CB-BE76-4199969830BD@microsoft.com...
> > > Good afternoon, all,
> > >
> > > One column in my database ("ColStatus") is validated by a drop down
list,
> > > and depending on the value selected, I want the interior colour for
all
> > > database cells in the target row to change. In the code shown below,
the
> > > msgboxes work fine, so the case logic is OK, but in each case, the
colour
> > > doesn't change.
> > > Can any clever person out there suggest why this might be?
> > > The worksheet isn't protected.
> > > Thanks in anticipation
> > >
> > > Pete
> > >
> > >
> > >
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > >
> > > Dim DBSheet As Worksheet
> > > Dim ColStatus As Range
> > >
> > > Set DBSheet = Sheets("Database")
> > > Set ColStatus = DBSheet.Range("ColStatus")
> > >
> > > Set Intersection = Intersect(Target, ColStatus)
> > > If Not Intersection Is Nothing Then
> > > Select Case Target.Formula
> > > Case "Withdrawn"
> > > MsgBox ("Withdrawn")
> > > Target.Offset(0, -12).Resize(1,
30).Interior.ColorIndex =
> > 3
> > > Target.Offset(0, -12).Resize(1, 30).Font.ColorIndex =
> > > xlAutomatic
> > > Case "Completed"
> > > MsgBox ("Completed")
> > > Target.Offset(0, -12).Resize(1,
30).Interior.ColorIndex =
> > 4
> > > Target.Offset(0, -12).Resize(1, 30).Font.ColorIndex =
> > > xlAutomatic
> > > Case "On Hold"
> > > MsgBox ("On Hold")
> > > Target.Offset(0, -12).Resize(1,
30).Interior.ColorIndex =
> > 45
> > > Target.Offset(0, -12).Resize(1, 30).Font.ColorIndex =
> > > xlAutomatic
> > > Case Else
> > > MsgBox ("Other")
> > > Target.Offset(0, -12).Resize(1,
30).Interior.ColorIndex =
> > 38
> > > Target.Offset(0, -12).Resize(1, 30).Font.ColorIndex =
> > > xlAutomatic
> > > End Select
> > > End If
> > > End Sub
> > >
> >
> >
> >

No, 2003 SP1

I know the program logic is OK because the message boxes work correctly.
I tried again, just to be sure.
Direct entry, cells color OK
Pick from a dropdown and they don't.
Going home now to rest my brain, but please don't forget me!
Thanks for your help

Pete


"Bob Phillips" wrote:

> Excel 97?
>
> --
> HTH
>
> Bob Phillips
>
> (remove xxx from email address if mailing direct)
>
> "Peter Rooney" <PeterRooney@discussions.microsoft.com> wrote in message
> news:2C9EE6E2-F33C-4695-8B16-24AC6E99774A@microsoft.com...
> > Bob,
> >
> > In answer to your question, Yes, BUT - an update:
> >
> > If I take the validation off a cell in this column and just type in
> > something like "On Hold", then the colouring works.
> > If the cell becomes "On Hold" as the result of selecting that value from a
> > dropdown list i.e. using data validation, then it doesn't.
> >
> > Does the worksheet change event work differently if a cell's value is
> > selected from a list, as against being entered directly?
> >
> > Thanks
> >
> > Pete
> >
> >
> >
> > "Bob Phillips" wrote:
> >
> > > Are you sure that
> > >
> > > Target.Offset(0, -12)
> > >
> > > is valid, that is that the change column has at least 12 columns left?
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (remove xxx from email address if mailing direct)
> > >
> > > "Peter Rooney" <PeterRooney@discussions.microsoft.com> wrote in message
> > > news:C56CBB92-0659-48CB-BE76-4199969830BD@microsoft.com...
> > > > Good afternoon, all,
> > > >
> > > > One column in my database ("ColStatus") is validated by a drop down
> list,
> > > > and depending on the value selected, I want the interior colour for
> all
> > > > database cells in the target row to change. In the code shown below,
> the
> > > > msgboxes work fine, so the case logic is OK, but in each case, the
> colour
> > > > doesn't change.
> > > > Can any clever person out there suggest why this might be?
> > > > The worksheet isn't protected.
> > > > Thanks in anticipation
> > > >
> > > > Pete
> > > >
> > > >
> > > >
> > > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > >
> > > > Dim DBSheet As Worksheet
> > > > Dim ColStatus As Range
> > > >
> > > > Set DBSheet = Sheets("Database")
> > > > Set ColStatus = DBSheet.Range("ColStatus")
> > > >
> > > > Set Intersection = Intersect(Target, ColStatus)
> > > > If Not Intersection Is Nothing Then
> > > > Select Case Target.Formula
> > > > Case "Withdrawn"
> > > > MsgBox ("Withdrawn")
> > > > Target.Offset(0, -12).Resize(1,
> 30).Interior.ColorIndex =
> > > 3
> > > > Target.Offset(0, -12).Resize(1, 30).Font.ColorIndex =
> > > > xlAutomatic
> > > > Case "Completed"
> > > > MsgBox ("Completed")
> > > > Target.Offset(0, -12).Resize(1,
> 30).Interior.ColorIndex =
> > > 4
> > > > Target.Offset(0, -12).Resize(1, 30).Font.ColorIndex =
> > > > xlAutomatic
> > > > Case "On Hold"
> > > > MsgBox ("On Hold")
> > > > Target.Offset(0, -12).Resize(1,
> 30).Interior.ColorIndex =
> > > 45
> > > > Target.Offset(0, -12).Resize(1, 30).Font.ColorIndex =
> > > > xlAutomatic
> > > > Case Else
> > > > MsgBox ("Other")
> > > > Target.Offset(0, -12).Resize(1,
> 30).Interior.ColorIndex =
> > > 38
> > > > Target.Offset(0, -12).Resize(1, 30).Font.ColorIndex =
> > > > xlAutomatic
> > > > End Select
> > > > End If
> > > > End Sub
> > > >
> > >
> > >
> > >
>
>
>

Can you send me the workbook before you go?

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Peter Rooney" <PeterRooney@discussions.microsoft.com> wrote in message
news:8FD294BF-DA92-43DA-BBA2-52168FD52F74@microsoft.com...
> No, 2003 SP1
>
> I know the program logic is OK because the message boxes work correctly.
> I tried again, just to be sure.
> Direct entry, cells color OK
> Pick from a dropdown and they don't.
> Going home now to rest my brain, but please don't forget me!
> Thanks for your help
>
> Pete
>
>
> "Bob Phillips" wrote:
>
> > Excel 97?
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove xxx from email address if mailing direct)
> >
> > "Peter Rooney" <PeterRooney@discussions.microsoft.com> wrote in message
> > news:2C9EE6E2-F33C-4695-8B16-24AC6E99774A@microsoft.com...
> > > Bob,
> > >
> > > In answer to your question, Yes, BUT - an update:
> > >
> > > If I take the validation off a cell in this column and just type in
> > > something like "On Hold", then the colouring works.
> > > If the cell becomes "On Hold" as the result of selecting that value
from a
> > > dropdown list i.e. using data validation, then it doesn't.
> > >
> > > Does the worksheet change event work differently if a cell's value is
> > > selected from a list, as against being entered directly?
> > >
> > > Thanks
> > >
> > > Pete
> > >
> > >
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > Are you sure that
> > > >
> > > > Target.Offset(0, -12)
> > > >
> > > > is valid, that is that the change column has at least 12 columns
left?
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > (remove xxx from email address if mailing direct)
> > > >
> > > > "Peter Rooney" <PeterRooney@discussions.microsoft.com> wrote in
message
> > > > news:C56CBB92-0659-48CB-BE76-4199969830BD@microsoft.com...
> > > > > Good afternoon, all,
> > > > >
> > > > > One column in my database ("ColStatus") is validated by a drop
down
> > list,
> > > > > and depending on the value selected, I want the interior colour
for
> > all
> > > > > database cells in the target row to change. In the code shown
below,
> > the
> > > > > msgboxes work fine, so the case logic is OK, but in each case, the
> > colour
> > > > > doesn't change.
> > > > > Can any clever person out there suggest why this might be?
> > > > > The worksheet isn't protected.
> > > > > Thanks in anticipation
> > > > >
> > > > > Pete
> > > > >
> > > > >
> > > > >
> > > > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > > >
> > > > > Dim DBSheet As Worksheet
> > > > > Dim ColStatus As Range
> > > > >
> > > > > Set DBSheet = Sheets("Database")
> > > > > Set ColStatus = DBSheet.Range("ColStatus")
> > > > >
> > > > > Set Intersection = Intersect(Target, ColStatus)
> > > > > If Not Intersection Is Nothing Then
> > > > > Select Case Target.Formula
> > > > > Case "Withdrawn"
> > > > > MsgBox ("Withdrawn")
> > > > > Target.Offset(0, -12).Resize(1,
> > 30).Interior.ColorIndex =
> > > > 3
> > > > > Target.Offset(0, -12).Resize(1,
30).Font.ColorIndex =
> > > > > xlAutomatic
> > > > > Case "Completed"
> > > > > MsgBox ("Completed")
> > > > > Target.Offset(0, -12).Resize(1,
> > 30).Interior.ColorIndex =
> > > > 4
> > > > > Target.Offset(0, -12).Resize(1,
30).Font.ColorIndex =
> > > > > xlAutomatic
> > > > > Case "On Hold"
> > > > > MsgBox ("On Hold")
> > > > > Target.Offset(0, -12).Resize(1,
> > 30).Interior.ColorIndex =
> > > > 45
> > > > > Target.Offset(0, -12).Resize(1,
30).Font.ColorIndex =
> > > > > xlAutomatic
> > > > > Case Else
> > > > > MsgBox ("Other")
> > > > > Target.Offset(0, -12).Resize(1,
> > 30).Interior.ColorIndex =
> > > > 38
> > > > > Target.Offset(0, -12).Resize(1,
30).Font.ColorIndex =
> > > > > xlAutomatic
> > > > > End Select
> > > > > End If
> > > > > End Sub
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Bob,

I can't - it contains confidential information.

I'll try and concoct a version of it that duplicates the error.

Regards

Pete



"Bob Phillips" wrote:

> Can you send me the workbook before you go?
>
> --
> HTH
>
> Bob Phillips
>
> (remove xxx from email address if mailing direct)
>
> "Peter Rooney" <PeterRooney@discussions.microsoft.com> wrote in message
> news:8FD294BF-DA92-43DA-BBA2-52168FD52F74@microsoft.com...
> > No, 2003 SP1
> >
> > I know the program logic is OK because the message boxes work correctly.
> > I tried again, just to be sure.
> > Direct entry, cells color OK
> > Pick from a dropdown and they don't.
> > Going home now to rest my brain, but please don't forget me!
> > Thanks for your help
> >
> > Pete
> >
> >
> > "Bob Phillips" wrote:
> >
> > > Excel 97?
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (remove xxx from email address if mailing direct)
> > >
> > > "Peter Rooney" <PeterRooney@discussions.microsoft.com> wrote in message
> > > news:2C9EE6E2-F33C-4695-8B16-24AC6E99774A@microsoft.com...
> > > > Bob,
> > > >
> > > > In answer to your question, Yes, BUT - an update:
> > > >
> > > > If I take the validation off a cell in this column and just type in
> > > > something like "On Hold", then the colouring works.
> > > > If the cell becomes "On Hold" as the result of selecting that value
> from a
> > > > dropdown list i.e. using data validation, then it doesn't.
> > > >
> > > > Does the worksheet change event work differently if a cell's value is
> > > > selected from a list, as against being entered directly?
> > > >
> > > > Thanks
> > > >
> > > > Pete
> > > >
> > > >
> > > >
> > > > "Bob Phillips" wrote:
> > > >
> > > > > Are you sure that
> > > > >
> > > > > Target.Offset(0, -12)
> > > > >
> > > > > is valid, that is that the change column has at least 12 columns
> left?
> > > > >
> > > > > --
> > > > > HTH
> > > > >
> > > > > Bob Phillips
> > > > >
> > > > > (remove xxx from email address if mailing direct)
> > > > >
> > > > > "Peter Rooney" <PeterRooney@discussions.microsoft.com> wrote in
> message
> > > > > news:C56CBB92-0659-48CB-BE76-4199969830BD@microsoft.com...
> > > > > > Good afternoon, all,
> > > > > >
> > > > > > One column in my database ("ColStatus") is validated by a drop
> down
> > > list,
> > > > > > and depending on the value selected, I want the interior colour
> for
> > > all
> > > > > > database cells in the target row to change. In the code shown
> below,
> > > the
> > > > > > msgboxes work fine, so the case logic is OK, but in each case, the
> > > colour
> > > > > > doesn't change.
> > > > > > Can any clever person out there suggest why this might be?
> > > > > > The worksheet isn't protected.
> > > > > > Thanks in anticipation
> > > > > >
> > > > > > Pete
> > > > > >
> > > > > >
> > > > > >
> > > > > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > > > >
> > > > > > Dim DBSheet As Worksheet
> > > > > > Dim ColStatus As Range
> > > > > >
> > > > > > Set DBSheet = Sheets("Database")
> > > > > > Set ColStatus = DBSheet.Range("ColStatus")
> > > > > >
> > > > > > Set Intersection = Intersect(Target, ColStatus)
> > > > > > If Not Intersection Is Nothing Then
> > > > > > Select Case Target.Formula
> > > > > > Case "Withdrawn"
> > > > > > MsgBox ("Withdrawn")
> > > > > > Target.Offset(0, -12).Resize(1,
> > > 30).Interior.ColorIndex =
> > > > > 3
> > > > > > Target.Offset(0, -12).Resize(1,
> 30).Font.ColorIndex =
> > > > > > xlAutomatic
> > > > > > Case "Completed"
> > > > > > MsgBox ("Completed")
> > > > > > Target.Offset(0, -12).Resize(1,
> > > 30).Interior.ColorIndex =
> > > > > 4
> > > > > > Target.Offset(0, -12).Resize(1,
> 30).Font.ColorIndex =
> > > > > > xlAutomatic
> > > > > > Case "On Hold"
> > > > > > MsgBox ("On Hold")
> > > > > > Target.Offset(0, -12).Resize(1,
> > > 30).Interior.ColorIndex =
> > > > > 45
> > > > > > Target.Offset(0, -12).Resize(1,
> 30).Font.ColorIndex =
> > > > > > xlAutomatic
> > > > > > Case Else
> > > > > > MsgBox ("Other")
> > > > > > Target.Offset(0, -12).Resize(1,
> > > 30).Interior.ColorIndex =
> > > > > 38
> > > > > > Target.Offset(0, -12).Resize(1,
> 30).Font.ColorIndex =
> > > > > > xlAutomatic
> > > > > > End Select
> > > > > > End If
> > > > > > End Sub
> > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

Bob.

A puzzle - I made a cut down version with a single column database and it
worked fine (after obviously cutting down the sizes of the ranges to be
coloured in).
I don't think my workbook has a corruption, as the cut down version was
based on it using Save As, then trimming it down.
There arent any other event macros in the workbook.
I'm stumped, for the moment.

Pete



"Bob Phillips" wrote:

> Can you send me the workbook before you go?
>
> --
> HTH
>
> Bob Phillips
>
> (remove xxx from email address if mailing direct)
>
> "Peter Rooney" <PeterRooney@discussions.microsoft.com> wrote in message
> news:8FD294BF-DA92-43DA-BBA2-52168FD52F74@microsoft.com...
> > No, 2003 SP1
> >
> > I know the program logic is OK because the message boxes work correctly.
> > I tried again, just to be sure.
> > Direct entry, cells color OK
> > Pick from a dropdown and they don't.
> > Going home now to rest my brain, but please don't forget me!
> > Thanks for your help
> >
> > Pete
> >
> >
> > "Bob Phillips" wrote:
> >
> > > Excel 97?
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (remove xxx from email address if mailing direct)
> > >
> > > "Peter Rooney" <PeterRooney@discussions.microsoft.com> wrote in message
> > > news:2C9EE6E2-F33C-4695-8B16-24AC6E99774A@microsoft.com...
> > > > Bob,
> > > >
> > > > In answer to your question, Yes, BUT - an update:
> > > >
> > > > If I take the validation off a cell in this column and just type in
> > > > something like "On Hold", then the colouring works.
> > > > If the cell becomes "On Hold" as the result of selecting that value
> from a
> > > > dropdown list i.e. using data validation, then it doesn't.
> > > >
> > > > Does the worksheet change event work differently if a cell's value is
> > > > selected from a list, as against being entered directly?
> > > >
> > > > Thanks
> > > >
> > > > Pete
> > > >
> > > >
> > > >
> > > > "Bob Phillips" wrote:
> > > >
> > > > > Are you sure that
> > > > >
> > > > > Target.Offset(0, -12)
> > > > >
> > > > > is valid, that is that the change column has at least 12 columns
> left?
> > > > >
> > > > > --
> > > > > HTH
> > > > >
> > > > > Bob Phillips
> > > > >
> > > > > (remove xxx from email address if mailing direct)
> > > > >
> > > > > "Peter Rooney" <PeterRooney@discussions.microsoft.com> wrote in
> message
> > > > > news:C56CBB92-0659-48CB-BE76-4199969830BD@microsoft.com...
> > > > > > Good afternoon, all,
> > > > > >
> > > > > > One column in my database ("ColStatus") is validated by a drop
> down
> > > list,
> > > > > > and depending on the value selected, I want the interior colour
> for
> > > all
> > > > > > database cells in the target row to change. In the code shown
> below,
> > > the
> > > > > > msgboxes work fine, so the case logic is OK, but in each case, the
> > > colour
> > > > > > doesn't change.
> > > > > > Can any clever person out there suggest why this might be?
> > > > > > The worksheet isn't protected.
> > > > > > Thanks in anticipation
> > > > > >
> > > > > > Pete
> > > > > >
> > > > > >
> > > > > >
> > > > > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > > > >
> > > > > > Dim DBSheet As Worksheet
> > > > > > Dim ColStatus As Range
> > > > > >
> > > > > > Set DBSheet = Sheets("Database")
> > > > > > Set ColStatus = DBSheet.Range("ColStatus")
> > > > > >
> > > > > > Set Intersection = Intersect(Target, ColStatus)
> > > > > > If Not Intersection Is Nothing Then
> > > > > > Select Case Target.Formula
> > > > > > Case "Withdrawn"
> > > > > > MsgBox ("Withdrawn")
> > > > > > Target.Offset(0, -12).Resize(1,
> > > 30).Interior.ColorIndex =
> > > > > 3
> > > > > > Target.Offset(0, -12).Resize(1,
> 30).Font.ColorIndex =
> > > > > > xlAutomatic
> > > > > > Case "Completed"
> > > > > > MsgBox ("Completed")
> > > > > > Target.Offset(0, -12).Resize(1,
> > > 30).Interior.ColorIndex =
> > > > > 4
> > > > > > Target.Offset(0, -12).Resize(1,
> 30).Font.ColorIndex =
> > > > > > xlAutomatic
> > > > > > Case "On Hold"
> > > > > > MsgBox ("On Hold")
> > > > > > Target.Offset(0, -12).Resize(1,
> > > 30).Interior.ColorIndex =
> > > > > 45
> > > > > > Target.Offset(0, -12).Resize(1,
> 30).Font.ColorIndex =
> > > > > > xlAutomatic
> > > > > > Case Else
> > > > > > MsgBox ("Other")
> > > > > > Target.Offset(0, -12).Resize(1,
> > > 30).Interior.ColorIndex =
> > > > > 38
> > > > > > Target.Offset(0, -12).Resize(1,
> 30).Font.ColorIndex =
> > > > > > xlAutomatic
> > > > > > End Select
> > > > > > End If
> > > > > > End Sub
> > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>