Only recognizing when a cell value changes
Upcoming Power Excel Seminars
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Only recognizing when a cell value changes

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I currently have a macro that starts when a change is made in a specific range on the worksheet.

    My problem is that the macro runs for every cell in the range.

    Sample - BT7 value changes to 4. Then the macro is triggered. It compares each cell value in the range to a set of criteria. I only want that specific cell to be recognized because only that cells data changed.

    Currently I use "If then" statements to direct from the Worksheet code to six separate 'sub' statements in macros.

    The data in the defined range will continue to change as the worksheet is used so i can't compare to a predetermined value.

    Is there any hope?

    Thanks Again

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

    Question on this:
    The data in the defined range will continue to change as the worksheet is used so i can't compare to a predetermined value.
    Do you need to reference a cell to gather the conditional value?
    Paste a sample of your code and the cell references which the code needs in order to make a decision.
    Give as much detail as your willing to give and I guarantee someone in here can solve your problem...

    Try this for now.
    Place a condition on running your macro...
    in change event


    If target.row = 7 and _
    target.column = 72 then'the 72 is Colum BT
    RunMyMacro
    end if

    Have a nice day
    Tom


    [ This Message was edited by: TsTom on 2002-03-26 05:34 ]

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks TsTom,

    I'll go that direction. Nice of you to help me out!


  4. #4
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

    If the Worksheet_Change event, try

    If Not Itersect(Target, Range("A1:D20")) Is Nothing Then
    'Do It!
    End If

  5. #5
    New Member
    Join Date
    Mar 2002
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    OK, TsTom here is my code. . . .beware that I am a novice and working at learning as I go. This is probably far from optimal.

    NOTE - I use =counta(E7:H7, I11:L11, M15:P15) in cell BT7 and other ranges in BT8 and so on. . . .


    *****************************************************************************************
    Private Sub Worksheet_Change(ByVal Target As Range)





    If Range("BT7") > 0 And Range("BT7") < 6 Then
    Call FillColor
    Else: Call Kladblok
    End If


    If Range("BT8") > 0 And Range("BT8") < 6 Then
    Call Fillcolor1
    Else: Call Kladblok1
    End If


    If Range("BT9") > 0 And Range("BT9") < 6 Then
    Call Fillcolor2
    Else: Call Kladblok2
    End If


    If Range("CA7") > 0 And Range("CA7") < 6 Then
    Call Fillcolor3
    Else: Call Kladblok3
    End If


    If Range("CA8") > 0 And Range("CA8") < 6 Then
    Call Fillcolor4
    Else: Call Kladblok4
    End If


    If Range("CA9") > 0 And Range("CA9") < 6 Then
    Call Fillcolor5
    Else: Call Kladblok5
    End If




    End Sub

    *******************************************************************************************


    Option Explicit

    Sub Fillcolor1()
    '
    ' Fillcolor1 Macro
    ' Macro recorded 26/03/2002 by K. A. Otting
    '

    '
    With Range("E8:F8,E8:H8,I12:L12,M16:P16").Interior
    .ColorIndex = 35
    .Pattern = xlSolid
    End With

    End Sub
    Sub Kladblok1()
    '
    ' Kladblok1 Macro
    ' Macro recorded 26/03/2002 by K. A. Otting
    '

    '

    With Range("E8:H8").Interior
    .ColorIndex = 15
    .Pattern = xlSolid
    End With
    With Range("I12:J12,I12:L12,M16:P16").Interior
    .ColorIndex = xlNone
    End With
    End Sub
    Sub Fillcolor2()
    '
    ' Fillcolor2 Macro
    ' Macro recorded 26/03/2002 by K. A. Otting
    '

    '
    With Range("E9:F9,E9:H9,I13:L13,M17:P17").Interior
    .ColorIndex = 36
    .Pattern = xlSolid
    End With

    End Sub
    Sub Kladblok2()
    '
    ' Kladblok2 Macro
    ' Macro recorded 26/03/2002 by K. A. Otting
    '

    '
    With Range("I13:L13,M17:P17").Interior
    .ColorIndex = xlNone
    End With
    Range("E9:H9").Interior.ColorIndex = 15

    End Sub
    *********************************************

    That is the idea. . . . I tried to add a copy of my spreadsheet but couldn't figure out how.

    Thanks for the help.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com