Formatting a cell with an if function and a formula

Hi,

I've been trying to find out if its possible to format a cell to 'if the word 'ASAP' is entered into that cell then it displays the current time at which the text was entered'.

Example
If in cell A1 i enter the text 'asap' then cell A1 should display the time at which the word 'asap' was entered into cell A1.

Thanks in advance
Suban

Thanks for the reply, but wouldn't that condition cell B1 based on cell A1s content?

I was wondering if it were possible to use formulas or functions to do this without using another cell.

Hi, is anyone else able to help with this please?

Pl see attached file.Worksheet_Change event is used.
Range used is A1:A3, can be changed in the code.

Thank you. that works well. is there a way to do this without using vba code and just using the functions of the excel?

Okno worries. thanks for your help guys

Hi all, so the vba written by kvsrinivasamurthy in the attachment above works well, however, when i try to delete the data entered in 2 or more cells i get a 'run-time error' 13': type mismatch. Could anyone help me with this error please?


Sent from my iPhone using Tapatalk

hi tony,

i don't quite understand your reply.

i'm trying to achieve a way of time stamping when the word asap is written in a cell. kvsrinivas has kindly sent me the vba code which is contained within the spreadsheet attached above, but the issue i have is after typing asap into 2 cells and then i highlight both cells and delete, i get a 'run-time error' 13': type mismatch.

hi,

thanks. this worked really well, but has one issue. after typing 'asap' into 2 cells and then i highlight both cells and delete, i get a 'run-time error' 13': type mismatch. anyway you could help?

Thanks
Suban

Ok thanks.


Sent from my iPhone using Tapatalk

Hi is anyone else able to help directly with the code?

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A1:A3")) Is Nothing Then

Application.EnableEvents = False

If UCase(Target.Value) = "ASAP" Then

Target.Value = Time

End If

Application.EnableEvents = True

End If

End Sub

Thanks in advance
Suban


Sent from my iPhone using Tapatalk

Adding Target.Count = 1 means that the code will only be triggered when one cell is being changed ... not when you are clearing many cells at once.

Please Login or Register  to view this content.

Thank you so much nimrod. If you have the time and you don't mind could you explain what your piece of code does.

Thanks


Sent from my iPhone using Tapatalk

Hello suban:

I added "Target.Count = 1" to the code. What this does is assure that your code for testing for the value "asap" only happens when you are only changing one cell (i.e. target count = 1). This way wwhen you are clearing many cells i.e. target.count > 1 , then you don't run your "asap" code ... which will blow-up when more then one cell is the target. Does that make sense ??

Ahhh i see. Yes that makes sense. Thanks again nimrod


Sent from my iPhone using Tapatalk