CODEWITHSUNDEEP
vbaexceldefault-value
aagaardist
aagaardist

Reputation: 29

VBA Default value in cell based on another cell

I'm pretty new to VBA, but found this code that inserts a default value. The problem is that i need to have a default value inserted in a column based on another column.

Say that in "Column A" it takes the value 1 if the row is active, and 0 if the row is inactive. If column "A1" = 1 it should insert 9999 in column "C1" if 0 it shouldn't do anything. Can anyone help me modify the code? 

Private Sub Worksheet_Change(ByVal Target As Range)
Dim C As Range, inter As Range, r As Range
Set C = Range("C9:C21")
Set inter = Intersect(C, Target)
If inter Is Nothing Then Exit Sub

Application.EnableEvents = False
  For Each r In inter
     If r.Value = "" Then r.Value = 9999
  Next r
Application.EnableEvents = True
End Sub

Again thank you

Upvotes: 1

Views: 2044

Answers (2)

QHarr
QHarr

Reputation: 84465

Try the following. It is based on detecting a change in column B and setting C according to A. I have set from cell B1 but you can adjust as necessary.

Not that it is terribly relevant but I checked the syntax for intersect against this

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim aRange As Range

    Set aRange = ActiveSheet.Range("B1:B21") 'Changed to B1 start

    If Not Application.Intersect(Target, aRange) Is Nothing Then

        If Target.Offset(, -1) = 1 Then          

            Target.Offset(, 1) = 9999

        ElseIf Target.Offset(, -1) = 0 Then 

            Target.Offset(, 1) = vbNullString

        End If

    End If

End Sub

Upvotes: 1

Middle
Middle

Reputation: 143

Check to see if this is something you are looking for:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim C As Range, inter As Range, r As Range
Set C = Range("C9:C21")
Set inter = Intersect(C, Target)
If inter Is Nothing Then Exit Sub

Application.EnableEvents = False
  For Each r In inter
     'this checks cell on the same row as r in column A
     If cells(r.row,1) = "Whatever values you need here" Then r.Value = 9999
     end if
  Next r
Application.EnableEvents = True
End Sub

Please clarify further if you want something else.

Upvotes: 0

Related Questions