Macro activated when a value in a cell changes

G

Guest

Have a column that contains a list for most cells (Y,N, Not applicable).
When a user answers C30 as "N" I would like several cells in column C and
column D to automatically populate with data. I did get this to work using
Worksheet_Change methods but the macro activates with changes to any cell on
that specific worksheet and I get errors when workbook macros attempt to
access this sheet.

I only want this action if the cell value is N. Alternatively if the user
selects Y I would like these cells to have their content cleared.

Here is my code. I could not figure out how to attach a short example of
this spreadsheet.

Any assistance would be most appreciated.

Thanks,

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target = [C30] Then

If Range("C30").Value = "N" Then

Range("C32").Value = "Not Applicable"
' Range("C32").Interior.ColorIndex = 30
Range("C33").Value = "Not Applicable"
' Range("C33").Interior.ColorIndex = 30
Range("C34").Value = "Not Applicable"
' Range("C34").Interior.ColorIndex = 30
Range("C35").Value = "Not Applicable"
' Range("C35").Interior.ColorIndex = 30
Range("C36").Value = "Not Applicable"
' Range("C36").Interior.ColorIndex = 30
Range("C38").Value = "Not Applicable"
' Range("C38").Interior.ColorIndex = 30
Range("C39").Value = "Not Applicable"
' Range("C39").Interior.ColorIndex = 30
Range("C40").Value = "Not Applicable"
' Range("C40").Interior.ColorIndex = 30
Range("C41").Value = "Not Applicable"
' Range("C41").Interior.ColorIndex = 30
Range("C42").Value = "Not Applicable"
' Range("C42").Interior.ColorIndex = 30

' Range("D31").Interior.ColorIndex = 30
Range("D31").Value = "Not Applicable"
' Range("D37").Interior.ColorIndex = 30
Range("D37").Value = "Not Applicable"
' Range("D43").Interior.ColorIndex = 30
Range("D43").Value = "Not Applicable"

ElseIf Range("C30").Value = "Y" Then

Range("C32").Value = Null
' Range("C32").Interior.ColorIndex = Null
Range("C33").Value = Null
' Range("C33").Interior.ColorIndex = Null
Range("C34").Value = Null
' Range("C34").Interior.ColorIndex = Null
Range("C35").Value = Null
' Range("C35").Interior.ColorIndex = Null
Range("C36").Value = Null
' Range("C36").Interior.ColorIndex = Null
Range("C38").Value = Null
' Range("C38").Interior.ColorIndex = Null
Range("C39").Value = Null
' Range("C39").Interior.ColorIndex = Null
Range("C40").Value = Null
' Range("C40").Interior.ColorIndex = Null
Range("C41").Value = Null
' Range("C41").Interior.ColorIndex = Null
Range("C42").Value = Null
' Range("C42").Interior.ColorIndex = Null

Range("D31").Interior.ColorIndex = Null
Range("D31").Value = Null
Range("D37").Interior.ColorIndex = Null
Range("D37").Value = Null
Range("D43").Interior.ColorIndex = Null
Range("D43").Value = Null

End If
End If

If Target = [C45] Then

If Range("C45").Value = "N" Then


Range("C47").Value = "Not Applicable"
' Range("C47").Interior.ColorIndex = 30
Range("C48").Value = "Not Applicable"
' Range("C48").Interior.ColorIndex = 30
Range("C49").Value = "Not Applicable"
' Range("C49").Interior.ColorIndex = 30
Range("C50").Value = "Not Applicable"
' Range("C50").Interior.ColorIndex = 30
Range("C52").Value = "Not Applicable"
' Range("C52").Interior.ColorIndex = 30
Range("C53").Value = "Not Applicable"
' Range("C53").Interior.ColorIndex = 30
Range("C54").Value = "Not Applicable"
' Range("C54").Interior.ColorIndex = 30
Range("C55").Value = "Not Applicable"
' Range("C55").Interior.ColorIndex = 30
Range("C56").Value = "Not Applicable"
' Range("C56").Interior.ColorIndex = 30

' Range("D46").Interior.ColorIndex = 30
Range("D46").Value = "Not Applicable"
' Range("D51").Interior.ColorIndex = 30
Range("D51").Value = "Not Applicable"
' Range("D57").Interior.ColorIndex = 30
Range("D57").Value = "Not Applicable"

ElseIf Range("C45").Value = "Y" Then

Range("C47").Value = Null
' Range("C47").Interior.ColorIndex = Null
Range("C48").Value = Null
' Range("C48").Interior.ColorIndex = Null
Range("C49").Value = Null
' Range("C49").Interior.ColorIndex = Null
Range("C50").Value = Null
' Range("C50").Interior.ColorIndex = Null
Range("C52").Value = Null
' Range("C52").Interior.ColorIndex = Null
Range("C53").Value = Null
' Range("C53").Interior.ColorIndex = Null
Range("C54").Value = Null
' Range("C54").Interior.ColorIndex = Null
Range("C55").Value = Null
' Range("C55").Interior.ColorIndex = Null
Range("C56").Value = Null
' Range("C56").Interior.ColorIndex = Null

' Range("D46").Interior.ColorIndex = Null
Range("D46").Value = Null
' Range("D51").Interior.ColorIndex = Null
Range("D51").Value = Null
' Range("D57").Interior.ColorIndex = Null
Range("D57").Value = Null

End If
End If


End Sub
 
G

Guest

Try something like this:

if not intersect(Target,range("C10")) is nothing then
'all activity if there the target is C10

if target.value = "N" then
target.offset(0,1).value = whatever 'enters data in first column to
right of target
else

end if

end if



CarlSh said:
Have a column that contains a list for most cells (Y,N, Not applicable).
When a user answers C30 as "N" I would like several cells in column C and
column D to automatically populate with data. I did get this to work using
Worksheet_Change methods but the macro activates with changes to any cell on
that specific worksheet and I get errors when workbook macros attempt to
access this sheet.

I only want this action if the cell value is N. Alternatively if the user
selects Y I would like these cells to have their content cleared.

Here is my code. I could not figure out how to attach a short example of
this spreadsheet.

Any assistance would be most appreciated.

Thanks,

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target = [C30] Then

If Range("C30").Value = "N" Then

Range("C32").Value = "Not Applicable"
' Range("C32").Interior.ColorIndex = 30
Range("C33").Value = "Not Applicable"
' Range("C33").Interior.ColorIndex = 30
Range("C34").Value = "Not Applicable"
' Range("C34").Interior.ColorIndex = 30
Range("C35").Value = "Not Applicable"
' Range("C35").Interior.ColorIndex = 30
Range("C36").Value = "Not Applicable"
' Range("C36").Interior.ColorIndex = 30
Range("C38").Value = "Not Applicable"
' Range("C38").Interior.ColorIndex = 30
Range("C39").Value = "Not Applicable"
' Range("C39").Interior.ColorIndex = 30
Range("C40").Value = "Not Applicable"
' Range("C40").Interior.ColorIndex = 30
Range("C41").Value = "Not Applicable"
' Range("C41").Interior.ColorIndex = 30
Range("C42").Value = "Not Applicable"
' Range("C42").Interior.ColorIndex = 30

' Range("D31").Interior.ColorIndex = 30
Range("D31").Value = "Not Applicable"
' Range("D37").Interior.ColorIndex = 30
Range("D37").Value = "Not Applicable"
' Range("D43").Interior.ColorIndex = 30
Range("D43").Value = "Not Applicable"

ElseIf Range("C30").Value = "Y" Then

Range("C32").Value = Null
' Range("C32").Interior.ColorIndex = Null
Range("C33").Value = Null
' Range("C33").Interior.ColorIndex = Null
Range("C34").Value = Null
' Range("C34").Interior.ColorIndex = Null
Range("C35").Value = Null
' Range("C35").Interior.ColorIndex = Null
Range("C36").Value = Null
' Range("C36").Interior.ColorIndex = Null
Range("C38").Value = Null
' Range("C38").Interior.ColorIndex = Null
Range("C39").Value = Null
' Range("C39").Interior.ColorIndex = Null
Range("C40").Value = Null
' Range("C40").Interior.ColorIndex = Null
Range("C41").Value = Null
' Range("C41").Interior.ColorIndex = Null
Range("C42").Value = Null
' Range("C42").Interior.ColorIndex = Null

Range("D31").Interior.ColorIndex = Null
Range("D31").Value = Null
Range("D37").Interior.ColorIndex = Null
Range("D37").Value = Null
Range("D43").Interior.ColorIndex = Null
Range("D43").Value = Null

End If
End If

If Target = [C45] Then

If Range("C45").Value = "N" Then


Range("C47").Value = "Not Applicable"
' Range("C47").Interior.ColorIndex = 30
Range("C48").Value = "Not Applicable"
' Range("C48").Interior.ColorIndex = 30
Range("C49").Value = "Not Applicable"
' Range("C49").Interior.ColorIndex = 30
Range("C50").Value = "Not Applicable"
' Range("C50").Interior.ColorIndex = 30
Range("C52").Value = "Not Applicable"
' Range("C52").Interior.ColorIndex = 30
Range("C53").Value = "Not Applicable"
' Range("C53").Interior.ColorIndex = 30
Range("C54").Value = "Not Applicable"
' Range("C54").Interior.ColorIndex = 30
Range("C55").Value = "Not Applicable"
' Range("C55").Interior.ColorIndex = 30
Range("C56").Value = "Not Applicable"
' Range("C56").Interior.ColorIndex = 30

' Range("D46").Interior.ColorIndex = 30
Range("D46").Value = "Not Applicable"
' Range("D51").Interior.ColorIndex = 30
Range("D51").Value = "Not Applicable"
' Range("D57").Interior.ColorIndex = 30
Range("D57").Value = "Not Applicable"

ElseIf Range("C45").Value = "Y" Then

Range("C47").Value = Null
' Range("C47").Interior.ColorIndex = Null
Range("C48").Value = Null
' Range("C48").Interior.ColorIndex = Null
Range("C49").Value = Null
' Range("C49").Interior.ColorIndex = Null
Range("C50").Value = Null
' Range("C50").Interior.ColorIndex = Null
Range("C52").Value = Null
' Range("C52").Interior.ColorIndex = Null
Range("C53").Value = Null
' Range("C53").Interior.ColorIndex = Null
Range("C54").Value = Null
' Range("C54").Interior.ColorIndex = Null
Range("C55").Value = Null
' Range("C55").Interior.ColorIndex = Null
Range("C56").Value = Null
' Range("C56").Interior.ColorIndex = Null

' Range("D46").Interior.ColorIndex = Null
Range("D46").Value = Null
' Range("D51").Interior.ColorIndex = Null
Range("D51").Value = Null
' Range("D57").Interior.ColorIndex = Null
Range("D57").Value = Null

End If
End If


End Sub
Click to expand...
 
D

dq

Additionally add
Application.EnableEvents = False
at the begin of your function and
Application.EnableEvents = True
at the end. This will prevent Excel from calling your function for
every value you change in your function.

DQ
 
G

Guest

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error goto ErrHandler
Application.EnableEvents = False
If Target.Address = "$C$30" Then

If Range("C30").Value = "N" Then
Range("C32:C36,C38:C42,D31,D36,D43").Value = "Not Applicable"

ElseIf Range("C30").Value = "Y" Then

Range("C32:C36,C38:C42,D31,D36,D43").clearContents
End If
End If

If Target = "$C$45" Then

If Range("C45").Value = "N" Then
Range("C47:C50,C52:C56,D46,D51,D57").Value = _
"Not Applicable"


ElseIf Range("C45").Value = "Y" Then

Range("C47:C50,C52:C56,D46,D51,D57").ClearContents
End If
End If

Errhandler:
Application.EnableEvents = True

End Sub

--
Regards,
Tom Ogilvy


CarlSh said:
Have a column that contains a list for most cells (Y,N, Not applicable).
When a user answers C30 as "N" I would like several cells in column C and
column D to automatically populate with data. I did get this to work using
Worksheet_Change methods but the macro activates with changes to any cell on
that specific worksheet and I get errors when workbook macros attempt to
access this sheet.

I only want this action if the cell value is N. Alternatively if the user
selects Y I would like these cells to have their content cleared.

Here is my code. I could not figure out how to attach a short example of
this spreadsheet.

Any assistance would be most appreciated.

Thanks,

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target = [C30] Then

If Range("C30").Value = "N" Then

Range("C32").Value = "Not Applicable"
' Range("C32").Interior.ColorIndex = 30
Range("C33").Value = "Not Applicable"
' Range("C33").Interior.ColorIndex = 30
Range("C34").Value = "Not Applicable"
' Range("C34").Interior.ColorIndex = 30
Range("C35").Value = "Not Applicable"
' Range("C35").Interior.ColorIndex = 30
Range("C36").Value = "Not Applicable"
' Range("C36").Interior.ColorIndex = 30
Range("C38").Value = "Not Applicable"
' Range("C38").Interior.ColorIndex = 30
Range("C39").Value = "Not Applicable"
' Range("C39").Interior.ColorIndex = 30
Range("C40").Value = "Not Applicable"
' Range("C40").Interior.ColorIndex = 30
Range("C41").Value = "Not Applicable"
' Range("C41").Interior.ColorIndex = 30
Range("C42").Value = "Not Applicable"
' Range("C42").Interior.ColorIndex = 30

' Range("D31").Interior.ColorIndex = 30
Range("D31").Value = "Not Applicable"
' Range("D37").Interior.ColorIndex = 30
Range("D37").Value = "Not Applicable"
' Range("D43").Interior.ColorIndex = 30
Range("D43").Value = "Not Applicable"

ElseIf Range("C30").Value = "Y" Then

Range("C32").Value = Null
' Range("C32").Interior.ColorIndex = Null
Range("C33").Value = Null
' Range("C33").Interior.ColorIndex = Null
Range("C34").Value = Null
' Range("C34").Interior.ColorIndex = Null
Range("C35").Value = Null
' Range("C35").Interior.ColorIndex = Null
Range("C36").Value = Null
' Range("C36").Interior.ColorIndex = Null
Range("C38").Value = Null
' Range("C38").Interior.ColorIndex = Null
Range("C39").Value = Null
' Range("C39").Interior.ColorIndex = Null
Range("C40").Value = Null
' Range("C40").Interior.ColorIndex = Null
Range("C41").Value = Null
' Range("C41").Interior.ColorIndex = Null
Range("C42").Value = Null
' Range("C42").Interior.ColorIndex = Null

Range("D31").Interior.ColorIndex = Null
Range("D31").Value = Null
Range("D37").Interior.ColorIndex = Null
Range("D37").Value = Null
Range("D43").Interior.ColorIndex = Null
Range("D43").Value = Null

End If
End If

If Target = [C45] Then

If Range("C45").Value = "N" Then


Range("C47").Value = "Not Applicable"
' Range("C47").Interior.ColorIndex = 30
Range("C48").Value = "Not Applicable"
' Range("C48").Interior.ColorIndex = 30
Range("C49").Value = "Not Applicable"
' Range("C49").Interior.ColorIndex = 30
Range("C50").Value = "Not Applicable"
' Range("C50").Interior.ColorIndex = 30
Range("C52").Value = "Not Applicable"
' Range("C52").Interior.ColorIndex = 30
Range("C53").Value = "Not Applicable"
' Range("C53").Interior.ColorIndex = 30
Range("C54").Value = "Not Applicable"
' Range("C54").Interior.ColorIndex = 30
Range("C55").Value = "Not Applicable"
' Range("C55").Interior.ColorIndex = 30
Range("C56").Value = "Not Applicable"
' Range("C56").Interior.ColorIndex = 30

' Range("D46").Interior.ColorIndex = 30
Range("D46").Value = "Not Applicable"
' Range("D51").Interior.ColorIndex = 30
Range("D51").Value = "Not Applicable"
' Range("D57").Interior.ColorIndex = 30
Range("D57").Value = "Not Applicable"

ElseIf Range("C45").Value = "Y" Then

Range("C47").Value = Null
' Range("C47").Interior.ColorIndex = Null
Range("C48").Value = Null
' Range("C48").Interior.ColorIndex = Null
Range("C49").Value = Null
' Range("C49").Interior.ColorIndex = Null
Range("C50").Value = Null
' Range("C50").Interior.ColorIndex = Null
Range("C52").Value = Null
' Range("C52").Interior.ColorIndex = Null
Range("C53").Value = Null
' Range("C53").Interior.ColorIndex = Null
Range("C54").Value = Null
' Range("C54").Interior.ColorIndex = Null
Range("C55").Value = Null
' Range("C55").Interior.ColorIndex = Null
Range("C56").Value = Null
' Range("C56").Interior.ColorIndex = Null

' Range("D46").Interior.ColorIndex = Null
Range("D46").Value = Null
' Range("D51").Interior.ColorIndex = Null
Range("D51").Value = Null
' Range("D57").Interior.ColorIndex = Null
Range("D57").Value = Null

End If
End If


End Sub
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

AutoDetect the path of the file? 4
Linking Cells Macro 3
help with macro 5
Running a Macro when a value in a range of cell changes 10
Please check my code for conditional formatting 2
Run-Time error '-2147417848 (80010108)': Automation error The object invoked has disconnected from 0
Run-Time error '-2147417848 (80010108)': Automation error The object invoked has disconnected from 0
Macro to tidy data 4

Top