Greetings,

The goal is to walk/run either 3 miles per day or 40 miles bi-weekly (20 miles per week).
I’ve attached the sample worksheet I created and would like to modify it as follows:

1.When I enter the # of miles each day and it is <3 miles, let’s say 2 miles, then I would like to display a text saying “…almost you’re short by a mile to meet your goal..”

2. The next day if # of miles = 3, then I would like a message saying “great, you meet your goal today…”
3. If # of mile run >3, let’s say 5 miles, then the txt message would read “ excellent, you’ve exceeded your daily goal by 2 miles)

The same message for bi-weekly (bi-weekly #1 a mile short, and bi-weekly #2 exceeded the 40 miles goal by 5 miles)

OCM

Displaying a message or a comment each time I enter a value would make me rather nervous, and it doesn't work for the bi-weekly values because those are calculated, not entered.
Moreover, it would require macro code.

The attached version demonstrates two alternatives that don't use VBA:

- Formulas to display a text in the cell to the right of the number of miles.
- Conditional formatting to color the cells with the number of miles.

You could add something like this to the worksheet object in code.
Any time to change a cell in Col B, E, H etc it will popup a message and then check the value in row 13 and popup a message about how the biweekly number is going.

Steve

<pre>Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim dValue As Double
Dim rCell As Range
For Each rCell In Target
If (rCell.Column - 2) Mod 3 = 0 Then
dValue = rCell.Value
Select Case dValue
Case Is < 3
MsgBox "...almost you’re short by " & _
3 - dValue & " mile(s) to meet your goal..."
Case Is = 3
MsgBox "great, you met your goal today..."
Case Is > 3
MsgBox "excellent, you’ve exceeded your daily goal by " & _
dValue - 3 & " mile(s)"
End Select
dValue = Me.Cells(13, rCell.Column) + _
Me.Cells(13, rCell.Column + 3)
Select Case dValue
Case Is < 40
MsgBox "...almost you’re short by " & _
40 - dValue & " mile(s) to meet your biweekly goal..."
Case Is = 40
MsgBox "great, you met your biweekly goal today..."
Case Is > 40
MsgBox "excellent, you’ve exceeded your biweekly goal by " & _
dValue - 40 & " mile(s)"
End Select
End If
Next
Set rCell = Nothing
End Sub</pre>

Steve,
Thank you for your reply post. Just want to make sure the following:
1. I just need to copy & paste your code into the worksheet (right click worksheet tab & view code & past it there, or alt + F11 & paste it there correct?
2. how do i hide this code so that no one can modify it?

Thanks,
OCM

Hans,
Thank you very much it works beautifuly!

OCM

Yes the view code in the sheet gets you there directly.

While in VB you can protect the project:
Right-click the sheet object in "Project Explorer" pane
Select "VBAProject Properties..."
Select "Protection" tab
Check the box: "Lock project for viewing"
Enter and confirm a password in the boxes.
[OK] to confirm

[Note: remember this password or you will not be able to access the code later]

Steve