ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Macro activated when a value in a cell changes (https://www.excelbanter.com/excel-programming/388776-macro-activated-when-value-cell-changes.html)

CarlSh
Macro activated when a value in a cell changes
 
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

Barb Reinhardt
Macro activated when a value in a cell changes
 
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" wrote:

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

dq
Macro activated when a value in a cell changes
 
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

Tom Ogilvy
Macro activated when a value in a cell changes
 

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" wrote:

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


All times are GMT +1. The time now is 08:03 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com