If I'm using the following code how would I modify it so that the date is only inserted when a specific word is put into a cell?

/**
* Creates a Date Stamp if a column is edited.
*/
//CORE VARIABLES
// The column you want to check if something is entered.
var COLUMNTOCHECK = 1;
// Where you want the date time stamp offset from the input location. [row, column]
var DATETIMELOCATION = [0,3];
// Sheet you are working on
var SHEETNAME = 'Sheet1'
function onEdit(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
//checks that we're on the correct sheet.
if( sheet.getSheetName() == SHEETNAME ) {
var selectedCell = ss.getActiveCell();
//checks the column to ensure it is on the one we want to cause the date to appear.
if( selectedCell.getColumn() == COLUMNTOCHECK) {
var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
dateTimeCell.setValue(new Date());
}
}
}

In article <8f59e925-d332-499d-ba1a-dab9d48536dcn@googlegroups.com>,
jared.schmid@visitbicycleworld.com says...
>
> If I'm using the following code how would I modify it so that the date is only inserted when a specific word is put into a cell?
>
> /**
> * Creates a Date Stamp if a column is edited.
> */
>
> //CORE VARIABLES
> // The column you want to check if something is entered.
> var COLUMNTOCHECK = 1;
> // Where you want the date time stamp offset from the input location. [row, column]
> var DATETIMELOCATION = [0,3];
> // Sheet you are working on
> var SHEETNAME = 'Sheet1'
>
> function onEdit(e) {
> var ss = SpreadsheetApp.getActiveSpreadsheet();
> var sheet = ss.getActiveSheet();
> //checks that we're on the correct sheet.
> if( sheet.getSheetName() == SHEETNAME ) {
> var selectedCell = ss.getActiveCell();
> //checks the column to ensure it is on the one we want to cause the date to appear.
> if( selectedCell.getColumn() == COLUMNTOCHECK) {
> var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
> dateTimeCell.setValue(new Date());
> }
> }
> }

Interesting puzzle. Rather than focus on the code you have here, I prefer to
stand back and work from the desired goal, which I presume is to keep a record
of the last change date of any cell - if the latest value matches some string.

Most of my VBA has been done in other applications than Excel, so no
guarantees!

Firstly, I see Events operate at the Worksheet level, rather than per cell or
range. So I'd put my code into the event handler for the "worksheet.change"
Event:

The standard event handler, always called "Worksheet_Change", returns a value
"Target", which is the range (one or more cells) whose change triggered the
Event.

These web pages explain how to do that:
https://www.thesmallman.com/vba-worksheet-change-events (nice video)
https://docs.microsoft.com/en-us/office/vba/api/excel.worksheet.change
https://docs.microsoft.com/en-us/office/troubleshoot/excel/run-macro-cells-
change
https://www.extendoffice.com/documents/excel/3884-excel-monitor-cell-
changes.html
http://www.cpearson.com/excel/Events.aspx

You'd then look to see if the latest value of the cell or cells matches your
string of interest (lots of functions to play with to do that) and then take
whatever action you want. Personally, I'd want to consider commenting the cell
itself rather than writing to another cell - see this:
https://docs.microsoft.com/en-us/office/vba/api/excel.range.addcomment

I note your code relies on the ActiveCell: I'm not confident that this would
always be the changed cell. If you start editing a cell, and then click
elsewhere (or tab to next cell) your changes will be retained but the
ActiveCell would surely be the one you just entered? I can't find anything
online to say either way!

Hope that helps.
--

Phil, London