Cell Value Change

[Paul Senior]

Hi,

I am using excel 97 and need some help to achive the following.

When a cell value is changed I want a 'Y' to be entered in a given cell further across on that row. I have mutiple rows but the 'Y' that is entered must be on the same row as where the cell value has changed.

If any one can help I will be very happy

[Leith Ross]

Hello Paul,

Will it always be the same column in that row?

Thanks,
Leith Ross

[Paul Senior]

Yes, Same column but different rows.

Hope you can help.

Many thans in anticipation

[Leith Ross]

Hello Paul,

To do this requires writing some code for the Worksheet_Change() event. Here is an example. This can be changed to meet your needs.

Whenever a cell is changed, this will place a "Y" in Column "E" of the same Row.

Private Sub Worksheet_Change(ByVal Target As Range)

Cells(Target.Row, "E").Value = "Y"

End Sub

Sincerely,
Leith Ross

[Paul Senior]

Leith,

Thank you for this, I cant believe how simple that was.

How do I now limit this to columns B:AR

Also is it possible to have a 'Y' entered if the formating of the above colums changes?

Once again hope you can assist

[Leith Ross]

Hello Paul,

The cell format won't effect the cell's value, other than it's appearance. The "Y" will always be stored in the cell.

I am not clear on what you want to do with columns B:AR. Can you give me some more details?

Thanks,
Leith Ross

[Paul Senior]

Hi Leith,

This works well but now I only want a 'Y' placed in the cell (E in your example) if the value of columns B:AR changes, therefore changes to columns oustide this range will not place a 'Y' in the given cell.

As for the format side what I am trying to do is also have the 'Y' enetered in the given cell if the format changes even if the value doesn't.

Thanks again

[Leith Ross]

Hello Paul,

Don't know about the Format changes. Here's the code to restrict the columns.

Private Sub Worksheet_Change(ByVal Target As Range)

C = Target.Column
C1 = Range("B1").Column
C2 = Range("AR1").Column

If C>=C1 And C<=C2 Then
Cells(Target.Row, C).Value = "Y"
End If

End Sub

Sincerely,
Leith Ross

[Paul Senior]

Hi Leith,

Once again may thanks for your help.

Do you think there is a way of applying this logic as I mentioned to changes in format?

The next step problem I need to solve relates to the code below:

Sub Mail_Product_Data_Update()
Dim varAnswer As String

varAnswer = MsgBox("Are you sure you want to send Updates? If Yes then Select Yes on the next Warning Message.", vbYesNo, "Warning")
If varAnswer = vbNo Then Exit Sub
Dim strDate As String
Sheets(Array("Product Data")).Copy
strDate = Format(Date, "dd-mm-yy") & " " & Format(Time, "h-mm-ss")
ActiveWorkbook.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strDate & ".xls"
ActiveWorkbook.SendMail "[email protected]", _
"Updated Combined Report"
ActiveWorkbook.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
ActiveWorkbook.Close False
End Sub

This sends the whole worksheet, however I now want to be able to just select the rows that have a 'Y' in a set column and then email this.

Any ideas how i may acieve this?

I must say your assitance is truely appreciated.

Regards

Paul