 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / February 2007Can Excel create a conditional Comment on a cell?
Can Excel create a conditional Comment on a cell? i.e. if cell value is above a value, create a comment ("Value is Very High = 'Cell Value' ") On Feb 16, 10:05 am, Desmond <Desm...@discussions.microsoft.com> wrote: > Can Excel create a conditional Comment on a cell? i.e. if cell value is above > a value, create a comment ("Value is Very High = 'Cell Value' ") Yes- use IF function Example Column A put 1,2,3,4,5 A1:A5 Column B IF (A1>2, "too high", "too low") copy B1 to B1:B5 Only through the use of a Macro if you're thinking of a comment type popup. That could be done with code similar to this using the Worksheet_Change() event handler (assumes you are watching for values .gt. 150 in cell B5 on a sheet) : Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address <> Range("B5").Address Then Exit Sub End If If Target > 150 Then Target.ClearComments ' erase any old Target.AddComment Target.Comment.Visible = False Target.Comment.Text Text:="The value is above 150" Else Target.ClearComments End If End Sub Now, if you just want a visible flag to indicate when a value goes above/below or reaches a specific value, then you could look into Conditional Formatting. You can use that [via Format | Conditional Formatting in the menu toolbar] to set the appearance of a cell based on the value of the cell itself. This might be a better solution for you since it involves no coding, is easier to apply to a large number of cells, especially if those cells are spread out around the worksheet. > Can Excel create a conditional Comment on a cell? i.e. if cell value is above > a value, create a comment ("Value is Very High = 'Cell Value' ") Thanks. I already use conditional formatting and I cap the value so that it does not skew my average, but I want to keep a history of the original value in a pop up comment. I was hoping to stay away from vba as other users get freaked out when the open the spreadsheet and it warns them that 'Macro's may have a virus". > Only through the use of a Macro if you're thinking of a comment type popup. > That could be done with code similar to this using the Worksheet_Change() [quoted text clipped - 27 lines] > > Can Excel create a conditional Comment on a cell? i.e. if cell value is above > > a value, create a comment ("Value is Very High = 'Cell Value' ") I understand that issue - not much to be done about that in this case, I'm afraid. I don't know of any other way to add a comment to a cell automatically except via code, having tried clean living, wishful thinking and even sacrificing small reptiles - all to no avail <g> > Thanks. I already use conditional formatting and I cap the value so that it > does not skew my average, but I want to keep a history of the original value [quoted text clipped - 33 lines] > > > Can Excel create a conditional Comment on a cell? i.e. if cell value is above > > > a value, create a comment ("Value is Very High = 'Cell Value' ") |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.