Weird formula request

I would like to have a column that records the date that another
column ("Status") was populated with a value.

The column titled "Status," when populated with a value, marks the
row
a certain color depending on the text entered in the field. For
example, if I enter "cb" (meaning 'call back') the row turns a light
yellow. There are about 10 different possibilities that could be
entered and colors that correspond with the entered text.


Regardless of the text entered into the "Status" column, I would like
another column's field in the same row to be automatically populated
with that day's date when the "Status" column is first populated
(regardless of the entered text) AND I do not want that date to ever
change, even if the text of the "Status" column is changed or
deleted.


Is this possible?


Thanks in advance for your help!


magmike



0
magmike7 (50)
8/29/2011 1:29:22 AM
excel 39879 articles. 2 followers. Follow

12 Replies
640 Views

Similar Articles

[PageSpeed] 38

You cannot do that with a formula Mike.

You must use VBA.............specifically event code.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    On Error GoTo enditall
    Application.EnableEvents = False
    If Target.Cells.Column = 1 Then  '1 is column A
        n = Target.Row
        If Excel.Range("A" & n).Value <> "" _
              And Excel.Range("B" & n).Value = "" Then
        Excel.Range("B" & n).Value = Format(Date, "mm-dd-yyyy")
        End If
    End If
enditall:
    Application.EnableEvents = True
End Sub

This is sheet event code.  Right-click on the sheet tab and "View
Code"

Copy/paste the code into that sheet module.

Edit the columns A and B to adjust for your needs.


Gord Dibben    Microsoft Excel MVP

On Sun, 28 Aug 2011 18:29:22 -0700 (PDT), magmike <magmike7@yahoo.com>
wrote:

>I would like to have a column that records the date that another
>column ("Status") was populated with a value.
>
>The column titled "Status," when populated with a value, marks the
>row
>a certain color depending on the text entered in the field. For
>example, if I enter "cb" (meaning 'call back') the row turns a light
>yellow. There are about 10 different possibilities that could be
>entered and colors that correspond with the entered text.
>
>
>Regardless of the text entered into the "Status" column, I would like
>another column's field in the same row to be automatically populated
>with that day's date when the "Status" column is first populated
>(regardless of the entered text) AND I do not want that date to ever
>change, even if the text of the "Status" column is changed or
>deleted.
>
>
>Is this possible?
>
>
>Thanks in advance for your help!
>
>
>magmike
>
>
0
phnorton (279)
8/29/2011 3:56:35 AM
On Aug 28, 10:56=A0pm, Gord <phnor...@shaw.ca> wrote:
> You cannot do that with a formula Mike.
>
> You must use VBA.............specifically event code.
>
> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> =A0 =A0 On Error GoTo enditall
> =A0 =A0 Application.EnableEvents =3D False
> =A0 =A0 If Target.Cells.Column =3D 1 Then =A0'1 is column A
> =A0 =A0 =A0 =A0 n =3D Target.Row
> =A0 =A0 =A0 =A0 If Excel.Range("A" & n).Value <> "" _
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 And Excel.Range("B" & n).Value =3D "" Then
> =A0 =A0 =A0 =A0 Excel.Range("B" & n).Value =3D Format(Date, "mm-dd-yyyy")
> =A0 =A0 =A0 =A0 End If
> =A0 =A0 End If
> enditall:
> =A0 =A0 Application.EnableEvents =3D True
> End Sub
>
> This is sheet event code. =A0Right-click on the sheet tab and "View
> Code"
>
> Copy/paste the code into that sheet module.
>
> Edit the columns A and B to adjust for your needs.
>
> Gord Dibben =A0 =A0Microsoft Excel MVP
>
> On Sun, 28 Aug 2011 18:29:22 -0700 (PDT), magmike <magmi...@yahoo.com>
> wrote:
>
>
>
> >I would like to have a column that records the date that another
> >column ("Status") was populated with a value.
>
> >The column titled "Status," when populated with a value, marks the
> >row
> >a certain color depending on the text entered in the field. For
> >example, if I enter "cb" (meaning 'call back') the row turns a light
> >yellow. There are about 10 different possibilities that could be
> >entered and colors that correspond with the entered text.
>
> >Regardless of the text entered into the "Status" column, I would like
> >another column's field in the same row to be automatically populated
> >with that day's date when the "Status" column is first populated
> >(regardless of the entered text) AND I do not want that date to ever
> >change, even if the text of the "Status" column is changed or
> >deleted.
>
> >Is this possible?
>
> >Thanks in advance for your help!
>
> >magmike- Hide quoted text -
>
> - Show quoted text -

Thanks. That works great!
0
magmike7 (50)
8/29/2011 2:35:14 PM
On Aug 28, 10:56=A0pm, Gord <phnor...@shaw.ca> wrote:
> You cannot do that with a formula Mike.
>
> You must use VBA.............specifically event code.
>
> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> =A0 =A0 On Error GoTo enditall
> =A0 =A0 Application.EnableEvents =3D False
> =A0 =A0 If Target.Cells.Column =3D 1 Then =A0'1 is column A
> =A0 =A0 =A0 =A0 n =3D Target.Row
> =A0 =A0 =A0 =A0 If Excel.Range("A" & n).Value <> "" _
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 And Excel.Range("B" & n).Value =3D "" Then
> =A0 =A0 =A0 =A0 Excel.Range("B" & n).Value =3D Format(Date, "mm-dd-yyyy")
> =A0 =A0 =A0 =A0 End If
> =A0 =A0 End If
> enditall:
> =A0 =A0 Application.EnableEvents =3D True
> End Sub
>
> This is sheet event code. =A0Right-click on the sheet tab and "View
> Code"
>
> Copy/paste the code into that sheet module.
>
> Edit the columns A and B to adjust for your needs.
>
> Gord Dibben =A0 =A0Microsoft Excel MVP
>
> On Sun, 28 Aug 2011 18:29:22 -0700 (PDT), magmike <magmi...@yahoo.com>
> wrote:
>
>
>
> >I would like to have a column that records the date that another
> >column ("Status") was populated with a value.
>
> >The column titled "Status," when populated with a value, marks the
> >row
> >a certain color depending on the text entered in the field. For
> >example, if I enter "cb" (meaning 'call back') the row turns a light
> >yellow. There are about 10 different possibilities that could be
> >entered and colors that correspond with the entered text.
>
> >Regardless of the text entered into the "Status" column, I would like
> >another column's field in the same row to be automatically populated
> >with that day's date when the "Status" column is first populated
> >(regardless of the entered text) AND I do not want that date to ever
> >change, even if the text of the "Status" column is changed or
> >deleted.
>
> >Is this possible?
>
> >Thanks in advance for your help!
>
> >magmike- Hide quoted text -
>
> - Show quoted text -

PS: Can I have multiple versions of this code on the same sheet? I
would also like to have another date column to record when a specific
word is typed into the Status column. For example, I modified it for a
second date column to record the date that the word "quote" was
entered into column 5. However, either I modified it wrong, or this
code only works once? Here is how it appears in my code sheet (with
both codes present):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    On Error GoTo enditall
    Application.EnableEvents =3D False
    If Target.Cells.Column =3D 5 Then  '1 is column A
        n =3D Target.Row
        If Excel.Range("E" & n).Value <> "" _
              And Excel.Range("M" & n).Value =3D "" Then
        Excel.Range("M" & n).Value =3D Format(Date, "mm-dd-yyyy")
        End If
    End If
enditall:
    Application.EnableEvents =3D True
End Sub


Private Sub Worksheet_ChangeQTE(ByVal Target As Excel.Range)
    On Error GoTo enditall
    Application.EnableEvents =3D False
    If Target.Cells.Column =3D 5 Then  '1 is column A
        n =3D Target.Row
        If Excel.Range("E" & n).Value =3D "quote" _
              And Excel.Range("N" & n).Value =3D "" Then
        Excel.Range("N" & n).Value =3D Format(Date, "mm-dd-yyyy")
        End If
    End If
enditall:
    Application.EnableEvents =3D True
End Sub
0
magmike7 (50)
8/29/2011 2:46:10 PM
magmike has brought this to us :
> On Aug 28, 10:56�pm, Gord <phnor...@shaw.ca> wrote:
>> You cannot do that with a formula Mike.
>> 
>> You must use VBA.............specifically event code.
>> 
>> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
>> � � On Error GoTo enditall
>> � � Application.EnableEvents = False
>> � � If Target.Cells.Column = 1 Then �'1 is column A
>> � � � � n = Target.Row
>> � � � � If Excel.Range("A" & n).Value <> "" _
>> � � � � � � � And Excel.Range("B" & n).Value = "" Then
>> � � � � Excel.Range("B" & n).Value = Format(Date, "mm-dd-yyyy")
>> � � � � End If
>> � � End If
>> enditall:
>> � � Application.EnableEvents = True
>> End Sub
>> 
>> This is sheet event code. �Right-click on the sheet tab and "View
>> Code"
>> 
>> Copy/paste the code into that sheet module.
>> 
>> Edit the columns A and B to adjust for your needs.
>> 
>> Gord Dibben � �Microsoft Excel MVP
>> 
>> On Sun, 28 Aug 2011 18:29:22 -0700 (PDT), magmike <magmi...@yahoo.com>
>> wrote:
>> 
>> 
>> 
>>> I would like to have a column that records the date that another
>>> column ("Status") was populated with a value.
>>> The column titled "Status," when populated with a value, marks the
>>> row
>>> a certain color depending on the text entered in the field. For
>>> example, if I enter "cb" (meaning 'call back') the row turns a light
>>> yellow. There are about 10 different possibilities that could be
>>> entered and colors that correspond with the entered text.
>>> Regardless of the text entered into the "Status" column, I would like
>>> another column's field in the same row to be automatically populated
>>> with that day's date when the "Status" column is first populated
>>> (regardless of the entered text) AND I do not want that date to ever
>>> change, even if the text of the "Status" column is changed or
>>> deleted.
>> 
>>> Is this possible?
>> 
>>> Thanks in advance for your help!
>> 
>>> magmike- Hide quoted text -
>> 
>> - Show quoted text -
>
> PS: Can I have multiple versions of this code on the same sheet? I
> would also like to have another date column to record when a specific
> word is typed into the Status column. For example, I modified it for a
> second date column to record the date that the word "quote" was
> entered into column 5. However, either I modified it wrong, or this
> code only works once? Here is how it appears in my code sheet (with
> both codes present):

Yes! Just change the structure of the inner If...Then:

> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
>   On Error GoTo enditall
>   Application.EnableEvents = False
>   If Target.Cells.Column = 5 Then  '1 is column A
>     n = Target.Row
>     If Excel.Range("E" & n).Value <> "" _
>        And Excel.Range("M" & n).Value = "" Then
>          Excel.Range("M" & n).Value = Format(Date, "mm-dd-yyyy")

      ElseIf Excel.Range("E" & n).Value = "quote" _
             And Excel.Range("N" & n).Value = "" Then
               Excel.Range("N" & n).Value = Format(Date, "mm-dd-yyyy")

>       End If
>     End If
> enditall:
>     Application.EnableEvents = True
> End Sub

-- 
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc


0
gs3102 (378)
8/29/2011 3:08:53 PM
On Aug 29, 10:08=A0am, GS <g...@somewhere.net> wrote:
> magmike has brought this to us :
>
>
>
>
>
> > On Aug 28, 10:56=A0pm, Gord <phnor...@shaw.ca> wrote:
> >> You cannot do that with a formula Mike.
>
> >> You must use VBA.............specifically event code.
>
> >> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> >> =A0 =A0 On Error GoTo enditall
> >> =A0 =A0 Application.EnableEvents =3D False
> >> =A0 =A0 If Target.Cells.Column =3D 1 Then =A0'1 is column A
> >> =A0 =A0 =A0 =A0 n =3D Target.Row
> >> =A0 =A0 =A0 =A0 If Excel.Range("A" & n).Value <> "" _
> >> =A0 =A0 =A0 =A0 =A0 =A0 =A0 And Excel.Range("B" & n).Value =3D "" Then
> >> =A0 =A0 =A0 =A0 Excel.Range("B" & n).Value =3D Format(Date, "mm-dd-yyy=
y")
> >> =A0 =A0 =A0 =A0 End If
> >> =A0 =A0 End If
> >> enditall:
> >> =A0 =A0 Application.EnableEvents =3D True
> >> End Sub
>
> >> This is sheet event code. =A0Right-click on the sheet tab and "View
> >> Code"
>
> >> Copy/paste the code into that sheet module.
>
> >> Edit the columns A and B to adjust for your needs.
>
> >> Gord Dibben =A0 =A0Microsoft Excel MVP
>
> >> On Sun, 28 Aug 2011 18:29:22 -0700 (PDT), magmike <magmi...@yahoo.com>
> >> wrote:
>
> >>> I would like to have a column that records the date that another
> >>> column ("Status") was populated with a value.
> >>> The column titled "Status," when populated with a value, marks the
> >>> row
> >>> a certain color depending on the text entered in the field. For
> >>> example, if I enter "cb" (meaning 'call back') the row turns a light
> >>> yellow. There are about 10 different possibilities that could be
> >>> entered and colors that correspond with the entered text.
> >>> Regardless of the text entered into the "Status" column, I would like
> >>> another column's field in the same row to be automatically populated
> >>> with that day's date when the "Status" column is first populated
> >>> (regardless of the entered text) AND I do not want that date to ever
> >>> change, even if the text of the "Status" column is changed or
> >>> deleted.
>
> >>> Is this possible?
>
> >>> Thanks in advance for your help!
>
> >>> magmike- Hide quoted text -
>
> >> - Show quoted text -
>
> > PS: Can I have multiple versions of this code on the same sheet? I
> > would also like to have another date column to record when a specific
> > word is typed into the Status column. For example, I modified it for a
> > second date column to record the date that the word "quote" was
> > entered into column 5. However, either I modified it wrong, or this
> > code only works once? Here is how it appears in my code sheet (with
> > both codes present):
>
> Yes! Just change the structure of the inner If...Then:
>
> > Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> > =A0 On Error GoTo enditall
> > =A0 Application.EnableEvents =3D False
> > =A0 If Target.Cells.Column =3D 5 Then =A0'1 is column A
> > =A0 =A0 n =3D Target.Row
> > =A0 =A0 If Excel.Range("E" & n).Value <> "" _
> > =A0 =A0 =A0 =A0And Excel.Range("M" & n).Value =3D "" Then
> > =A0 =A0 =A0 =A0 =A0Excel.Range("M" & n).Value =3D Format(Date, "mm-dd-y=
yyy")
>
> =A0 =A0 =A0 ElseIf Excel.Range("E" & n).Value =3D "quote" _
> =A0 =A0 =A0 =A0 =A0 =A0 =A0And Excel.Range("N" & n).Value =3D "" Then
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Excel.Range("N" & n).Value =3D Format(Date=
, "mm-dd-yyyy")
>
> > =A0 =A0 =A0 End If
> > =A0 =A0 End If
> > enditall:
> > =A0 =A0 Application.EnableEvents =3D True
> > End Sub
>
> --
> Garry
>
> Free usenet access athttp://www.eternal-september.org
> ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -
>
> - Show quoted text -

Fantastic - this is great! One last thing...

By doing this, I notice that if my first entry into the "E" column is
"quote", then it puts the date into column M, but NOT column N at the
same time. I also created a third column (Column O) for the "sent"
code (entered in column E) and the same thing. If I were to start by
inserting "cb", and then rewriting column E with "quote" and then
rewriting column E with "Sent" - it all works perfect - however, I may
end up speaking with a prospect and sending the quote right then and
there while I am on the phone. in that situation i would START by
typing "Sent" into the E Column and would want both columns N and O to
populate with the date.

How do I do that?

Thanks,
0
magmike7 (50)
8/29/2011 6:22:05 PM
On Aug 29, 1:22=A0pm, magmike <magmi...@yahoo.com> wrote:
> On Aug 29, 10:08=A0am, GS <g...@somewhere.net> wrote:
>
>
>
>
>
> > magmike has brought this to us :
>
> > > On Aug 28, 10:56=A0pm, Gord <phnor...@shaw.ca> wrote:
> > >> You cannot do that with a formula Mike.
>
> > >> You must use VBA.............specifically event code.
>
> > >> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> > >> =A0 =A0 On Error GoTo enditall
> > >> =A0 =A0 Application.EnableEvents =3D False
> > >> =A0 =A0 If Target.Cells.Column =3D 1 Then =A0'1 is column A
> > >> =A0 =A0 =A0 =A0 n =3D Target.Row
> > >> =A0 =A0 =A0 =A0 If Excel.Range("A" & n).Value <> "" _
> > >> =A0 =A0 =A0 =A0 =A0 =A0 =A0 And Excel.Range("B" & n).Value =3D "" Th=
en
> > >> =A0 =A0 =A0 =A0 Excel.Range("B" & n).Value =3D Format(Date, "mm-dd-y=
yyy")
> > >> =A0 =A0 =A0 =A0 End If
> > >> =A0 =A0 End If
> > >> enditall:
> > >> =A0 =A0 Application.EnableEvents =3D True
> > >> End Sub
>
> > >> This is sheet event code. =A0Right-click on the sheet tab and "View
> > >> Code"
>
> > >> Copy/paste the code into that sheet module.
>
> > >> Edit the columns A and B to adjust for your needs.
>
> > >> Gord Dibben =A0 =A0Microsoft Excel MVP
>
> > >> On Sun, 28 Aug 2011 18:29:22 -0700 (PDT), magmike <magmi...@yahoo.co=
m>
> > >> wrote:
>
> > >>> I would like to have a column that records the date that another
> > >>> column ("Status") was populated with a value.
> > >>> The column titled "Status," when populated with a value, marks the
> > >>> row
> > >>> a certain color depending on the text entered in the field. For
> > >>> example, if I enter "cb" (meaning 'call back') the row turns a ligh=
t
> > >>> yellow. There are about 10 different possibilities that could be
> > >>> entered and colors that correspond with the entered text.
> > >>> Regardless of the text entered into the "Status" column, I would li=
ke
> > >>> another column's field in the same row to be automatically populate=
d
> > >>> with that day's date when the "Status" column is first populated
> > >>> (regardless of the entered text) AND I do not want that date to eve=
r
> > >>> change, even if the text of the "Status" column is changed or
> > >>> deleted.
>
> > >>> Is this possible?
>
> > >>> Thanks in advance for your help!
>
> > >>> magmike- Hide quoted text -
>
> > >> - Show quoted text -
>
> > > PS: Can I have multiple versions of this code on the same sheet? I
> > > would also like to have another date column to record when a specific
> > > word is typed into the Status column. For example, I modified it for =
a
> > > second date column to record the date that the word "quote" was
> > > entered into column 5. However, either I modified it wrong, or this
> > > code only works once? Here is how it appears in my code sheet (with
> > > both codes present):
>
> > Yes! Just change the structure of the inner If...Then:
>
> > > Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> > > =A0 On Error GoTo enditall
> > > =A0 Application.EnableEvents =3D False
> > > =A0 If Target.Cells.Column =3D 5 Then =A0'1 is column A
> > > =A0 =A0 n =3D Target.Row
> > > =A0 =A0 If Excel.Range("E" & n).Value <> "" _
> > > =A0 =A0 =A0 =A0And Excel.Range("M" & n).Value =3D "" Then
> > > =A0 =A0 =A0 =A0 =A0Excel.Range("M" & n).Value =3D Format(Date, "mm-dd=
-yyyy")
>
> > =A0 =A0 =A0 ElseIf Excel.Range("E" & n).Value =3D "quote" _
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0And Excel.Range("N" & n).Value =3D "" Then
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Excel.Range("N" & n).Value =3D Format(Da=
te, "mm-dd-yyyy")
>
> > > =A0 =A0 =A0 End If
> > > =A0 =A0 End If
> > > enditall:
> > > =A0 =A0 Application.EnableEvents =3D True
> > > End Sub
>
> > --
> > Garry
>
> > Free usenet access athttp://www.eternal-september.org
> > ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text =
-
>
> > - Show quoted text -
>
> Fantastic - this is great! One last thing...
>
> By doing this, I notice that if my first entry into the "E" column is
> "quote", then it puts the date into column M, but NOT column N at the
> same time. I also created a third column (Column O) for the "sent"
> code (entered in column E) and the same thing. If I were to start by
> inserting "cb", and then rewriting column E with "quote" and then
> rewriting column E with "Sent" - it all works perfect - however, I may
> end up speaking with a prospect and sending the quote right then and
> there while I am on the phone. in that situation i would START by
> typing "Sent" into the E Column and would want both columns N and O to
> populate with the date.
>
> How do I do that?
>
> Thanks,- Hide quoted text -
>
> - Show quoted text -

Also, is there a way to ignore case? This method seems case sensitive
0
magmike7 (50)
8/29/2011 7:17:21 PM
Try this...

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  Dim n As Long, s As String
  On Error GoTo enditall
  If Target.Column = 5 Then  '1 is column A
    Application.EnableEvents = False
    n = Target.Row: s = UCase$(Target)
    Select Case s
      Case "QUOTE", Range("M" & n) = ""
       Range("M" & n).Resize(, 2) = Format(Date, "mm-dd-yyyy")

      Case "SENT", Range("N" & n) = ""
       Range("N" & n).Resize(, 2) = Format(Date, "mm-dd-yyyy")
    End Select

enditall:
    Application.EnableEvents = True
  End If
End Sub

-- 
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc


0
gs3102 (378)
8/29/2011 8:38:12 PM
On Aug 29, 3:38=A0pm, GS <g...@somewhere.net> wrote:
> Try this...
>
> Option Explicit
>
> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> =A0 Dim n As Long, s As String
> =A0 On Error GoTo enditall
> =A0 If Target.Column =3D 5 Then =A0'1 is column A
> =A0 =A0 Application.EnableEvents =3D False
> =A0 =A0 n =3D Target.Row: s =3D UCase$(Target)
> =A0 =A0 Select Case s
> =A0 =A0 =A0 Case "QUOTE", Range("M" & n) =3D ""
> =A0 =A0 =A0 =A0Range("M" & n).Resize(, 2) =3D Format(Date, "mm-dd-yyyy")
>
> =A0 =A0 =A0 Case "SENT", Range("N" & n) =3D ""
> =A0 =A0 =A0 =A0Range("N" & n).Resize(, 2) =3D Format(Date, "mm-dd-yyyy")
> =A0 =A0 End Select
>
> enditall:
> =A0 =A0 Application.EnableEvents =3D True
> =A0 End If
> End Sub
>
> --
> Garry
>
> Free usenet access athttp://www.eternal-september.org
> ClassicVB Users Regroup! comp.lang.basic.visual.misc

I have tried to modify this to fit my situation and have not had much
luck. I'm not sure where I have gone wrong, but here is my attempt:
NOTE: I need Column M to populate with a date regardless of what I
type in the first time, and if that initial text corresponds with
another one of the Case instructions below, that column should be
populated as well.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  Dim n As Long, s As String
  On Error GoTo enditall
  If Target.Column =3D 5 Then  '1 is column A
    Application.EnableEvents =3D False
    n =3D Target.Row: s =3D UCase$(Target)
    Select Case s
      'Case "", Range("M" & n) =3D ""
       'Range("M" & n).Resize(, 2) =3D Format(Date, "mm-dd-yyyy")

      Case "in", Range("N" & n) =3D ""
       Range("N" & n).Resize(, 2) =3D Format(Date, "mm-dd-yyyy")

      Case "quote", Range("O" & n) =3D ""
       Range("O" & n).Resize(, 2) =3D Format(Date, "mm-dd-yyyy")

      Case "sent", Range("P" & n) =3D ""
       Range("P" & n).Resize(, 2) =3D Format(Date, "mm-dd-yyyy")

      Case "req", Range("Q" & n) =3D ""
       Range("Q" & n).Resize(, 2) =3D Format(Date, "mm-dd-yyyy")

      Case "done", Range("R" & n) =3D ""
       Range("R" & n).Resize(, 2) =3D Format(Date, "mm-dd-yyyy")
    End Select

enditall:
    Application.EnableEvents =3D True
  End If
End Sub
0
magmike7 (50)
8/29/2011 9:47:35 PM
Mike,
You did state that you wanted the entries to NOT be 'case sensitive', 
and so if you look at how the string variable 's' is loaded it contains 
the 'Target.Value' in UPPERCASE. This means your Select Case checks 
MUST ALSO be UPPERCASE.

Here's revised code that I think will do what you want...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  Dim n As Long, s As String
  On Error GoTo enditall
  If Target.Column = 5 Then  '1 is column A
    Application.EnableEvents = False
    n = Target.Row: s = UCase$(Target)
    Range("M" & n) = Format(Date, "mm-dd-yyyy")
    Select Case s
      Case "IN", Range("N" & n) = ""
       Range("N" & n) = Format(Date, "mm-dd-yyyy")

      Case "QUOTE", Range("O" & n) = ""
       Range("O" & n) = Format(Date, "mm-dd-yyyy")

      Case "SENT", Range("P" & n) = ""
       Range("P" & n) = Format(Date, "mm-dd-yyyy")

      Case "REQ", Range("Q" & n) = ""
       Range("Q" & n) = Format(Date, "mm-dd-yyyy")

      Case "DONE", Range("R" & n) = ""
       Range("R" & n) = Format(Date, "mm-dd-yyyy")
    End Select

enditall:
    Application.EnableEvents = True
  End If
End Sub

-- 
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc


0
gs3102 (378)
8/30/2011 12:17:35 AM
On Aug 29, 7:17=A0pm, GS <g...@somewhere.net> wrote:
> Mike,
> You did state that you wanted the entries to NOT be 'case sensitive',
> and so if you look at how the string variable 's' is loaded it contains
> the 'Target.Value' in UPPERCASE. This means your Select Case checks
> MUST ALSO be UPPERCASE.
>
> Here's revised code that I think will do what you want...
>
> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> =A0 Dim n As Long, s As String
> =A0 On Error GoTo enditall
> =A0 If Target.Column =3D 5 Then =A0'1 is column A
> =A0 =A0 Application.EnableEvents =3D False
> =A0 =A0 n =3D Target.Row: s =3D UCase$(Target)
> =A0 =A0 Range("M" & n) =3D Format(Date, "mm-dd-yyyy")
> =A0 =A0 Select Case s
> =A0 =A0 =A0 Case "IN", Range("N" & n) =3D ""
> =A0 =A0 =A0 =A0Range("N" & n) =3D Format(Date, "mm-dd-yyyy")
>
> =A0 =A0 =A0 Case "QUOTE", Range("O" & n) =3D ""
> =A0 =A0 =A0 =A0Range("O" & n) =3D Format(Date, "mm-dd-yyyy")
>
> =A0 =A0 =A0 Case "SENT", Range("P" & n) =3D ""
> =A0 =A0 =A0 =A0Range("P" & n) =3D Format(Date, "mm-dd-yyyy")
>
> =A0 =A0 =A0 Case "REQ", Range("Q" & n) =3D ""
> =A0 =A0 =A0 =A0Range("Q" & n) =3D Format(Date, "mm-dd-yyyy")
>
> =A0 =A0 =A0 Case "DONE", Range("R" & n) =3D ""
> =A0 =A0 =A0 =A0Range("R" & n) =3D Format(Date, "mm-dd-yyyy")
> =A0 =A0 End Select
>
> enditall:
> =A0 =A0 Application.EnableEvents =3D True
> =A0 End If
> End Sub
>
> --
> Garry
>
> Free usenet access athttp://www.eternal-september.org
> ClassicVB Users Regroup! comp.lang.basic.visual.misc

Perfect!
0
magmike7 (50)
8/30/2011 2:43:10 AM
magmike formulated the question :
> On Aug 29, 7:17�pm, GS <g...@somewhere.net> wrote:
>> Mike,
>> You did state that you wanted the entries to NOT be 'case sensitive',
>> and so if you look at how the string variable 's' is loaded it contains
>> the 'Target.Value' in UPPERCASE. This means your Select Case checks
>> MUST ALSO be UPPERCASE.
>> 
>> Here's revised code that I think will do what you want...
>> 
>> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
>> � Dim n As Long, s As String
>> � On Error GoTo enditall
>> � If Target.Column = 5 Then �'1 is column A
>> � � Application.EnableEvents = False
>> � � n = Target.Row: s = UCase$(Target)
>> � � Range("M" & n) = Format(Date, "mm-dd-yyyy")
>> � � Select Case s
>> � � � Case "IN", Range("N" & n) = ""
>> � � � �Range("N" & n) = Format(Date, "mm-dd-yyyy")
>> 
>> � � � Case "QUOTE", Range("O" & n) = ""
>> � � � �Range("O" & n) = Format(Date, "mm-dd-yyyy")
>> 
>> � � � Case "SENT", Range("P" & n) = ""
>> � � � �Range("P" & n) = Format(Date, "mm-dd-yyyy")
>> 
>> � � � Case "REQ", Range("Q" & n) = ""
>> � � � �Range("Q" & n) = Format(Date, "mm-dd-yyyy")
>> 
>> � � � Case "DONE", Range("R" & n) = ""
>> � � � �Range("R" & n) = Format(Date, "mm-dd-yyyy")
>> � � End Select
>> 
>> enditall:
>> � � Application.EnableEvents = True
>> � End If
>> End Sub
>> 
>> --
>> Garry
>> 
>> Free usenet access athttp://www.eternal-september.org
>> ClassicVB Users Regroup! comp.lang.basic.visual.misc
>
> Perfect!

Great! ..glad to help.

-- 
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc


0
gs3102 (378)
8/30/2011 4:01:16 AM
On Aug 29, 11:01=A0pm, GS <g...@somewhere.net> wrote:
> magmike formulated the question :
>
>
>
>
>
> > On Aug 29, 7:17=A0pm, GS <g...@somewhere.net> wrote:
> >> Mike,
> >> You did state that you wanted the entries to NOT be 'case sensitive',
> >> and so if you look at how the string variable 's' is loaded it contain=
s
> >> the 'Target.Value' in UPPERCASE. This means your Select Case checks
> >> MUST ALSO be UPPERCASE.
>
> >> Here's revised code that I think will do what you want...
>
> >> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> >> =A0 Dim n As Long, s As String
> >> =A0 On Error GoTo enditall
> >> =A0 If Target.Column =3D 5 Then =A0'1 is column A
> >> =A0 =A0 Application.EnableEvents =3D False
> >> =A0 =A0 n =3D Target.Row: s =3D UCase$(Target)
> >> =A0 =A0 Range("M" & n) =3D Format(Date, "mm-dd-yyyy")
> >> =A0 =A0 Select Case s
> >> =A0 =A0 =A0 Case "IN", Range("N" & n) =3D ""
> >> =A0 =A0 =A0 =A0Range("N" & n) =3D Format(Date, "mm-dd-yyyy")
>
> >> =A0 =A0 =A0 Case "QUOTE", Range("O" & n) =3D ""
> >> =A0 =A0 =A0 =A0Range("O" & n) =3D Format(Date, "mm-dd-yyyy")
>
> >> =A0 =A0 =A0 Case "SENT", Range("P" & n) =3D ""
> >> =A0 =A0 =A0 =A0Range("P" & n) =3D Format(Date, "mm-dd-yyyy")
>
> >> =A0 =A0 =A0 Case "REQ", Range("Q" & n) =3D ""
> >> =A0 =A0 =A0 =A0Range("Q" & n) =3D Format(Date, "mm-dd-yyyy")
>
> >> =A0 =A0 =A0 Case "DONE", Range("R" & n) =3D ""
> >> =A0 =A0 =A0 =A0Range("R" & n) =3D Format(Date, "mm-dd-yyyy")
> >> =A0 =A0 End Select
>
> >> enditall:
> >> =A0 =A0 Application.EnableEvents =3D True
> >> =A0 End If
> >> End Sub
>
> >> --
> >> Garry
>
> >> Free usenet access athttp://www.eternal-september.org
> >> ClassicVB Users Regroup! comp.lang.basic.visual.misc
>
> > Perfect!
>
> Great! ..glad to help.
>
> --
> Garry
>
> Free usenet access athttp://www.eternal-september.org
> ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -
>
> - Show quoted text -

Got a wierd issue with this - it works - however, when I come in the
next day, it doesn't work anymore, but the code is still there and
correct. To get it to work again, I have to delete the code, save the
workbook, close it, reopen, repaste in the code and then it will work
again.

Any ideas?

PS: Was this one supposed to be under an Option Explicit?
magmike
0
magmike7 (50)
9/1/2011 2:39:10 PM
Reply:

Similar Artilces:

Weird Opening Document
Using Windows XP and Office 2007 I just received a Word 2003 document as an email attachment. When I opened the attachment, it launched Word 2003 on my system. Although Office 2003 still resides on my system, I am using Office 2007. I have received 2003 Word documents as email attachments before, and when I open the attachment it always launches Word 2007. Any idea what would cause an email attachment to launch Word 2003 this time??? My guess: Because the file extension was .doc and the default file format in Word 2007 is .docx Daddy "BK" <nosp...

weird symbol instead of comma in address?
I have Office 2007 and I am using the Publisher with the mail merge to populate the addresses in thank-you cards to be mailed out. The only problem is that the comma between the city and state does not look like a comma but a small right triangle without the hypotenuse. What do I need to do for the comma to appear correctly in the address? Are you using a decorative font? I can't duplicate your issue. Is the comma in the city field or are you manually adding it? Personally I don't use the comma any longer. -- Mary Sauer http://msauer.mvps.org/ "Susan" <dsnsac...

Pattern Formula?
I'm OK with understanding conditional formatting but my question is for adjacent cells. What I need to do is to enter a conditional value in one cell (in my case a "Y") and then have adjacent cells change their color pattern (in this case "black"). Can this be done somehow? Thanks -- 6-West yes in the first dropdown of the conditional formatting box, change the default value from 'cell value is' to 'formula is'.. Then input a formula that evaluates to T/f (like: $A1=1) That should do it! "sixwest" wrote: > I'm OK with unders...

WEIRD FOLDER
I have a weird folder under Local Disk/users/myname called: {bd85e97f-d63c-40fc-8915-0b1859d829e3} Is this folder needed? If not can I delete it? Thanks Paul "PAUL" <pkoukos@earthlink.net> wrote in message news:992BA7CD-6A82-46CD-9876-B7F69E8401D3@microsoft.com... > I have a weird folder under Local Disk/users/myname called: > {bd85e97f-d63c-40fc-8915-0b1859d829e3} > > Is this folder needed? If not can I delete it? > > Thanks > > Paul It's most likely a temp folder used by installing of some program. It should b...

copy formulae in excel
How do I copy a formulae to next rows without having Excel update the formulae in relation to the new cell? If you use $ signs in the function references, they won'tupdate on Copy -- Gary''s Student - gsnu201002 "beginner" wrote: > How do I copy a formulae to next rows without having Excel update the > formulae in relation to the new cell? Easiest way is to make the cell references absolute. If your formula looks like this: =A5 + B6 change it to =$A$5 + $B$6 and then you can copy it without changing the references. The $ makes the refer...

viewing formulas
I need to know how to display the formulas within my spreadsheet so that they can be printed to turn in for a class assignment Hi Tom See http://www.rondebruin.nl/print.htm#Formulas -- Regards Ron de Bruin http://www.rondebruin.nl "Tom Andrew" <TomAndrew@discussions.microsoft.com> wrote in message news:B95446C8-BB62-4C72-8097-B8A3CA86B23C@microsoft.com... >I need to know how to display the formulas within my spreadsheet so that they > can be printed to turn in for a class assignment ...

Modify the existing formula
Dear sir, There is 1 data set is assumed running in A1 down, viz.: In A1 down is: 1,2,3,4,5,6,7,8,9, and 10 In F1 down is: 20, 7, 1, 12 and 8 And I have a excel forumula in the cell M1 as below: {=IF(ROWS(M$1:M1)<=SUM(--(COUNTIF($F$1:$F$22,$A$1:$A$10)>0)),INDEX($A$1:$A$10, SMALL(IF(COUNTIF($F$1:$F$22,$A$1:$A$10)>0,ROW($A$1:$A$10)-MIN(ROW($A$1:$A$10)) +1),ROWS(M$1:M1))),"")} And then I drag the formula from M1 to M5. Therefore, the formula will give me the result "1" in cell M1, "7" in cell M2 and "8" in cell M3. The formula is extracted t...

Formula Help #39
I am using a way to do the following column1 column 2 column 3 (2+3)+(3*5) 5+15 20 in the first column I want to be able to see the expresion in the second column I want to have the result of each parenthesis and in the final column the result Please help Thanks Stelios ...

"A Formula or sheet..."
Windows Vista Ultimate - SP1 RC1 Office 2007 Professional When I try to copy a worksheet I get the following: "A formula or sheet you want to move or copy contains the name '_XXXX', which already exists on the destination worksheet. Do you want to use this version of the name?" This messages appears many time before the sheet is finally copied. I have been clicking the yes button and the task is completed. Is there a work-around, or add in, that allows me to go in and delete all these names so I can just copy the sheet w/o all the extra effort. It seems to me I ran i...

Weird shifting
I have two odd things happening in my document that I don't *think* are related, but who knows...? I'm using Word 2007 and I have a template that I created specifically for this document. It is a very convoluted template based on obsolete military style guides that I have no say in. I have 7 heading levels and each heading level has numbered procedural steps. Each heading level indents one step from the next higher heading, so by the time I get to the highest heading level my left indent is about 3". Crazy, but like I said, I have no say in the format. Anyhoo... w...

Weird Spacing Problem
I have two users that when they type an email in CRM and then click Save, it adds about 15 to 20 line spaces at the top of the document. To test, I used the same Contact, clicked email, then Save and it didn't do it for me. But then I had him go in to that Contact and open the email I had just created and it immediately added 15-20 lines at the top (that he can't get rid of). If he sends the email it leaves the lines and appears like it's a big, blank email. Anyone seen anything like this? I thought at first maybe it was an OL 2003 thing because I'm still on 2000, but I t...

Formula Assistance
I could use some guidance on this formula: {=SUM(IF((B$8:B$55="X")*(OR(J$8:J$55=2,J$8:J$55=9))*(IF(VLOOKUP(E$8:E$55,Codes!$M$2:$N$31,2,0)=1,1,0)),1,0))} I am trying to get a count of entries meeting three criteria. [1] If there is an "X" in column B [2] If the value in column J is a 2 or a 9 [3] If the value in column E is found in the vlookup(m2:m31) and the code(n2:31) is a '1' When I break it down and try running the parts, it seems to be fine. But as listed above the when parts [1] and [2] are true the entry is counted. This may be too much in...

percent formula
Anyone please! How do I write a formula that gives me the total after deducting %. Example if I have $25 and I need to deduct 14% and I need to show the total which would be 21.50 -- smile Try this: =25*(1-14%) A1 = 25 B1 = 14% =A1*(1-B1) Format as General or Number -- Biff Microsoft Excel MVP "israel" <israel@discussions.microsoft.com> wrote in message news:E5C67430-5992-46A1-B9EC-40C0DC443CDA@microsoft.com... > Anyone please! > > How do I write a formula that gives me the total after deducting %. > Example if I have $25 and I need to deduct 14% and I...

Formula is counting cell if nothing is entered
I have made a timesheet that will automatically round up to the nearest quarter hour, but if no time is entered the round column stays as 12:00 am and calcualtes as such. The first col. has the actual time then the second col. has the formula =round(E12*24/0.25,0)*0.25/24 which works to round the time, but, as I said, if nothing is entered in the time, the round col. shows 12:00 am. Hi Try one of the following modifications to your formula: =IF(E12="","",ROUND(E12*24/0.25,0)*0.25/24) =IF(ISBLANK(E12),"",ROUND(E12*24/0.25,0)*0.25/24) Regards, B. R. Rama...

Restore Result Of Cell Link After Every Time Run Formula
PHP code ------------------- RESTORE RESULT OF CELL LINK AFTER EVERY TIME RUN FORMULA IN COLUMN RESULT WITHOUT CHANGE OF BEFOR RESULT ------------------- FOR EXAMPLE : I WANT RESTORE RESULT OF : A1 CELL IS INPUT CELL NUMBER AND B1:B10 IS (LINK TO A1) COLUMN FOR EVERY TIME CHANGE A1 CONTEN BUT I WANT FILL B1:B10 AFTER TEN TIMES CHANGE A1 CONTENT IN THE OTHE WORD: EXAMPLE : A1 IS CONTENT 1 2 3 4 5 6 7 8 9 10 AND RESULT IN B1:B10 1 2 3 4 5 6 7 8 9 10 AFTER ANY TIME CLICK NE TYPE IN A +------------------------------------------------------------------- |Filename: EXCEL HELP ...

TODAY fucntion in a formula / conditoinal format? #2
Here's the condition I set: Formula=+$G7<=TODAY THe result was strange...when I autofilled it, it strated a dat sequence. What I mean is the original date in G7 is 7/31, when autofilled it, G8 became 8/1, G9, 8/2, G10 8/3 and so on. Where did screw it up -- bludovic ----------------------------------------------------------------------- bludovico's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1480 View this thread: http://www.excelforum.com/showthread.php?threadid=26505 ...

Weird scrolling problem with folders
I am having a really weird problem with my storage folders and scrolling to the bottom folder in the list. At the bottom of my email account list ( I have 15 email accounts, mostly imap), I have Outbox and underneath it Storage folders. I have a dozen or so folders in storage folders. When I expand storage folders and try to scroll down to the last item and click on it, the list will jump up , it will frequently jump back up a couple of items, and I can't see the bottom of the list. Does anyone have a bunch of folders in Storage folders, and does this happen to you? YOu have ...

Formula vs. Constant
Is there a way using excel funtions (not UDF) to find if a cell contains a formula vs. a number (constant). I looked around the forum but did not find anything. I tried using cell function and left function but they return the value in the formula itself. Any help is appreciated. Regards, RK hi you might consider the goto function. press F5. click the special button bottom right. check formula then ok. excel will high light all formulas. you can do the same with constrants. regards FSt1 "rk0909" wrote: > Is there a way using excel funtions (not UDF) to fin...

weird outlook 2007 shutdown problem
I run Outlook 2007 on Windows XP Pro SP2. When I reboot my computer ... the first time I start Outlook and go to close it ... It still runs in task manager. It doesn't use any CPU time so normally I would just say the heck with it but .... it does use anywhere from 66M to 127M of ram and I noticed when if I continue to let it run it slowly uses more and more ram... I can open and close it as many times as I want yet it still runs in TM. I have to end task on the process to get it to shut down .. Once I have ended task on the processes and launch Outlook again ... when I close it .. ...

Formulas #33
How do I create a formula that would alow me to select a value in one cell and subtract it from a value I enter in another cell. IE a product with 3 versions cost me 1.00, 2.00 or 3.00 depending on the version I want to create a forumla that alows me to select one of the build costs then enter a selling price in another cell and the 3rd cell would subtract the value of the build cost from the sales price and give me my margin. If your build cost is in A1, your sell price is in B1 and your profit margin is in C1 In C1 input =(b1-a1) Russell Dawson Excel Student Please ...

Weird Number Truncating
I am trying to create a spreadsheet for tracking items by barcode. Fo what ever reason, any number containing more than 15 numbers, exce changes all numbers after the 15th digit to zero. For example: 7301010250174422 becomes 7301010250174420 when entered into an excel cell. 7301010250174422456 becomes 7301010250174420000 Is there anyway to fix this? Thanks Darro -- Message posted from http://www.ExcelForum.com Hi Darron, Excel's precision for numbers is 15 decimal digits. Use text instead, either by preceding the number with a single quote (which will not show) or by formatting ...

Mailbox management -- recipient policy age limit weirdness
I've been testing a couple recipient policies, and I come across this kind of thing now and then. I end up with a report that shows a few users with something like this when absolutely nothing should get tagged for deletion on them: ---------- Cleaning Mailbox user@domain.com Recipient Policy: Test1 Folder / Contents: 1 Items (27.00 KB) Folder / Contents: 1 Items (27.00 KB) Folder / Done: 0 Items Processed, 1 Items Would Be Moved or Deleted (2097409 KB) Folder /Outbox Contents: 0 Items (5.00 KB) Folder /Outbox Contents: 0 Items (5.00 KB) Folder /Outbox Done: 0 Items Processed, 0 Items Wou...

Weird autoformat
We were just upgraded at work to Office 2002. I did not have this problem with earlier versions. I often enter text such as "A02-12" or "A01-03" into spreadsheets (this stands for Annealer 2, run 12 or Annealer 1, Run 3). However, now when I type it, for some reason Excel interprets that as a date with a very strange custom format. For example, A01-03, comes out look like a bunch of arabic with the number 3 after it. The format is B2d-mmm . If I change it to general format its the number 37686, which is the date March 6, 2003. Typing something like "B01-03&quo...

Time calc formula please.
Can you please tell me what the formula for this is please. A1 (start time) 0800 A2 (end time) 1515 A5 (total hrs=) 7.25 I need the formula for A5 TIA =24*(TEXT(A2,"00\:00")-TEXT(A1,"00\:00")) It would have been easier if you had put in your times as times, 08:00 and 15:15 -- David Biddulph "Mario" <imnot@home.com> wrote in message news:LAiun.17421$pv.7144@news-server.bigpond.net.au... > > Can you please tell me what the formula for this is please. > > A1 (start time) 0800 > A2 (end time) ...

Weird issue in CFileDialog(Context Menu)
hi I am running into this werid problem. I am using CFileDialog to open a file and it works fine if I either double click on the file or single click + "Open" button. But the thing is whenever I use the ContextMenu(right click) and "Select" to open the file, all the resource gets messed up(it seems like). right after I do right click->select to open up the file I call this CMenu menu; menu.LoadMenu(IDR_POPUP_MENU); and this LoadMenu returns false and every dialog text turn into a empty dialogbox(doesn't show any messages). why would the above code work when I ei...