PDA

View Full Version : How to change a date in one sheet and it add a value in another



scprism
08-13-2010, 07:58 AM
Sorry for the really confusing the title im not sure how to explain what im trying to do!

Ive been asked by a client to amend a spreadsheet so that when on a sheet a date is typed in, it puts an x in the right column in another sheet.

For example:


I put the date in Sheet 1 as 23/04/2010 next to a customers name
In Sheet 2 i would like an X to appear in a cell (lets say D3) under the April column for that customerIm not sure if this is possible but anyone taking the time to help me would me much appreciated!!

Thanks in advance :thumb

Shaun

Bob Phillips
08-13-2010, 08:22 AM
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "B:B"
Dim RowNum As Long
Dim ColNum As Long


If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

With Target

On Error Resume Next
RowNum = Application.Match(Target.Offset(0, -1).Value, Worksheets("Sheet2").Columns("A"), 0)
ColNum = Application.Match(CLng(DateSerial(Year(Target.Value), Month(Target.Value), 1)), Worksheets("Sheet2").Rows(1), 0)
On Error GoTo 0

If RowNum > 0 And ColNum > 0 Then

Worksheets("Sheet2").Cells(RowNum, ColNum).Value = "X"
End If
End With
End If
End Sub


This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.