Only recognizing when a cell value changes

[kotting]

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

[Tom Schreiner]

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 ]

[kotting]

Thanks TsTom,

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

[Dave Hawley]

Hi

If the Worksheet_Change event, try

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

[kotting]

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.