Can Excel create a conditional Comment on a cell?

[Guest]

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' ")

 

[jIM]

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

 

[Guest]

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.

 

[Guest]

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()
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' ")

 

[Guest]

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
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()
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' ")