Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Macro activated when a value in a cell changes
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,355
Default 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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
dq dq is offline
external usenet poster
  
Posts: 46
Default 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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default 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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
MACRO ACTIVATED AYTOMATICALLY Spiros Excel Discussion (Misc queries) 0 June 12th 08 07:25 AM
Macro to run when sheet activated Gary Keramidas Excel Programming 0 December 13th 06 09:27 PM
Macro to run when sheet activated Dave Miller Excel Programming 1 December 13th 06 09:26 PM
MACRO ACTIVATED EVERY 5 SECONDS CC Excel Discussion (Misc queries) 1 May 4th 06 03:02 PM
User Form activated by a macro mxleite Excel Programming 3 July 3rd 04 01:33 AM


All times are GMT +1. The time now is 11:19 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement