I have modified a couple of lines of code.
This code which starts:
Private Sub Worksheet_Change(ByVal Target As Range)always runs when any cell on that worksheet changes.
The code now tests to see if the change occurred on the Actual Average % row - Row 33.
It also tests to see if the percentage difference (one row below the Actual Average %) is zero or greater.
This is the first new line:
If Target.Row = 33 And Target.Offset(1, 0).Value >= 0 Then
(replaces If Target.Column = 5 And Target.Offset(0, 1).Text = "X" Then)
Target is the cell that changed, and Excel makes the target cell information available every time there is a change - this is an 'Event' driven process, referred to as an 'On Change event'
Knowing which cell changed the macro can determine what the change was and where it was. Knowing where it was allows related information to be checked - I use Offset (row, column) as a way to obtain the related information - as an offset from the target cell.
The second line I have changed is the one that creates the message:
.TextBody = "Item " & Target.Offset(-2, 0).Text & " is out of specification" & _
" ... " & "Max failures allowed is " & Target.Offset(-1, 0).Text & _
" ... " & "Actual failure rate is " & Target.Text
Replace the previous line that started '.TextBody='
Again Offset is used to get related information.
I had the item description on row 31, an offset of -2 rows from the target cell.
The 'Allowed %' was one row above and the actual value was the target cell itself - so no offset needed.
Excel allows me to obtain text directly from cells that contain values - so I can get what is showing in the cell (e.g., formatted numbers or percentages rather than the actual underlying value).
I had this in column D:
31 Widget - Alloy - Size #3
This was the body of the e-mail I received:
Item Widget - Alloy - Size #3 is out of specification ... Max failures allowed is 4.00%
... Actual failure rate is 4.02%
I would like to get carriage returns in the message, but no luck so far. I know that it is an issue - I just don't know how to resolve it!
(I tried <br /> and \n as well as vbCrLf)