Excel 2013 VBA programming

Question

I'll start off my saying I'm trying to save time for a family member who is doing this program manually.

Sheet 1

1. Column A is a start date
2. Column B is a projected start date
3. Column E is a Task Name.

Sheet 2 is a handmade calendar.

1. Tasks are in B8:B17 for Projected start date and B20:B29 for Actual start date
2. Week is C5:AK5, Month is C6:AK6, Day is C7:AK7

What I'm trying to do is:

IF cell change in sheet_1(range a2:a999)

Find in sheet 3 (C6:AK7) the location of value entered in sheet 2(active.cell)
    Store column number as Actual_Date_y
Active.Cell
    move active.cell to Location (R,C+4)
    Find in sheet 3 (B20:B29)the String from new active.cell
        Store Row Number as Actual_Date_x

print ("X"), in (Actual_Date_x,Actual_Date_y)

I'm a fish out of water when it comes to VBA.

@jcbermu - The project is 35 weeks long (C:AK is 35 columns). There is a week number (1-35) in row 5 (cells C5:AK5), then he has the month in C6:AK6, lastly he has the calender Sunday date in C7:AK7, as seen here:

EDIT: @Raystafarian, THANKS, this reads as exactly what I need. I'm going to try it and see what happens.

• @Raystafarian, "not found" every time. I'm going to say it's his format for the dates since there is no continuity between the sheets. I'm going to tweak his sheets and see if that fixes the issue.

Answer 1

Literally, what you want translates to something like this in the worksheet module for sheet 1 -

Sub worksheet_change(ByVal target As Range)

Dim actdatex As Integer
Dim actdatey As Integer
Dim newcell As Range
Dim rngdate As Range



If Not Intersect(target, Range("A2:A999")) Is Nothing Then
On Error GoTo handler
 For Each c In Range("Sheet3!C6:AK7")
    If c = Worksheets("Sheet2").Range(target.Address) Then
     actdatex = c.Column
     Exit For
    End If
 Next

 Set newcell = Range(target).Offset(, 4)

 For Each d In Range("Sheet3!B20:B29")
    If d = newcell Then
        actdatey = d.Row
    Exit For
    End If
 Next

 Set rngdate = Cells(actdatex, actdatey)
 rngdate = "X"

End If


handler:
MsgBox ("not found")
End Sub

Answer 2

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wkb As Workbook
Dim wks As Worksheet
Dim wks1 As Worksheet
Dim cell1, cell2, myrange As Range
Set wkb = ThisWorkbook
Set wks = wkb.Sheets(1)
Set wks1 = wkb.Sheets(2)
targetrow = Target.Row
targetcolumn = Target.Column
task = wks.Cells(targetrow, 3)
For i = 3 To 300
    a = wks1.Cells(6, i)
    If a = "" Then
        i = 301
    End If
    If a = Target.Value Then
        initialrow = 20
        If targetcolumn = 2 Then
            initialrow = initialrow - 12
        End If
        realrow = initialrow + targetrow - 2
        For j = 3 To 300
            wks1.Cells(realrow, j) = ""
            If wks1.Cells(6, j) = "" Then
                j = 301
            End If
        Next j
        wks1.Cells(realrow, i) = "X"
    End If
Next i
End Sub

It works only under some conditions:

1. On Sheet1 the columns must be in this order: Start Date | Projected Date | Task Name.

2. On Sheet2 the month and Sunday date must be the same.

   I will give you an example: On cell C6 and cell c7 you put 01/03/2015 and using cell format you select custom and use mmm on c6 and ddon C7.

3. The order of tasks must be the same on Sheet1 and Sheet2.

4. On Sheet2 the first task must be on cells B8 and B20.

The VBA code must be put on Sheet 1. You need to open Macros and on the left column, double click the worksheet, and then paste the code on the right side. Whenever a date changes on Sheet1 it is updated on Sheet2.