Changing values based on which cell is changed within a range.

Hi Guys,

I've got a list of passwords that need to be updated at different times, and I'm trying to keep track of them via excel.
The layout will have have Column A for Passwords, Column B for the number of days that the password will last before expiring, column C and D are the date that the password will expire, and Column E will contain the same as Column B.

The goal is that when the password is changed in Column A, Column C and D will update to show when the password will expire. Column E will show the same as Column B.
If the value in Column B changes, then Column C will show the result of "(Col-D minus Col-E) + Col-B". (Essentially adjusting the date that the password will expire.)

I hope this is making sense. If not, maybe the attached document will explain it better.

Currently I have this working using the following:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target = Cells(8, 1) Then
    Cells(8, 3) = Date + Cells(8, 2)
    Cells(8, 4) = Date + Cells(8, 2)
    Cells(8, 5) = Cells(8, 2)
    
    End If
    
    If Target = Cells(8, 2) Then
    Cells(8, 3) = (Cells(8, 4) - Cells(8, 5)) + Cells(8, 2)
    
    End If
End Sub

As you can see, this only works for Row 8. I've attempted to convert this to a range of cells and use offsets but have been unsuccessful. Can anyone assist in making this work for Rows 8-11 in the attached sheet, where it only updates the specific row that the change was made on. If I could use named ranges, that would be ideal, but not essential.

maybe so?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("A8:B11")) Is Nothing Then Exit Sub
Dim rw As Long: rw = Target.Row
Select Case Target.Column
    Case 1 'If Target = Cells(8, 1) Then
        Cells(rw, 3) = Date + Cells(rw, 2)
        Cells(rw, 4) = Date + Cells(rw, 2)
        Cells(rw, 5) = Cells(rw, 2)
    Case Else 'If Target = Cells(8, 2) Then
        Cells(rw, 3) = (Cells(rw, 4) - Cells(rw, 5)) + Cells(rw, 2)
End Select
End Sub

Thanks Nilem. Works great.

Is it at all possible to use a named range instead of specifying the cell range though?
The reason being, if I add a row at some point, meaning that I'm using rows 8 to 12 now, instead of 8 to 11, then row 12 isn't included.

In either case, thank you very much.

Try to write

If Intersect(Target, Range("nameRange")) Is Nothing Then Exit Sub

instead of

If Intersect(Target, Range("A8:B11")) Is Nothing Then Exit Sub

"nameRange" - your named range, for example, A8:B14

I tried to modify the code for the proper list and it didn't quite work. The below issues occurred.

* The date seems be starting from 01/01/1900 instead of todays date
* The 2 hidden columns aren't populating
* It doesn't seem to work if I set the range for columns 4 -6, but want the passwords in columns 4 and the days in columns 6.

I also need to break each range down into the different categories.
I've attached a small replica of the actual form that I will be using.

Any help would be appreciated.