Worksheet_Change для диапазона в диапазоне)

[mephist]

Доброго времени суток.

Пытаюсь реализовать следуюший механизм:
Если в диапазоне ячеек (E27;F128) меняется хотя бы одно значение
(как правило будут меняться все значения сразу), должен сработать макрос, который
в ячейку G27 вставит слово "Необходимость" при выполнении хотя бы одного условия: или E27>$E$17, или F27>$F$17
аналогично макрос должен сработать для G28 при условии E28>$E$17 или F28>$F$17
и т.д. до ячейки G128
Я постарался это реализовать следующим образом

Private Sub Worksheet_Change(ByVal Target As Range)
    For Each a In Range("G27:G128").Cells
    If (a.Offset(0, -2).Value > Range("e17").Value) Or (a.Offset(0, -1).Value > Range("f17").Value) Then
        a.Value = "Необходимость"
    End If
    Next
End Sub

Возникло 2 проблемы:
1. Когда выполняется одно из условий, макрос заносит в нужную ячейку слово "Необходимость", но после этого макрос начинает работать заново, так как на листе прозошли изменения-это приводит к зацикливанию. А необходимо, чтобы он допроверил весь дипазон до G128 до конца и везде внес изменения.
2. Как сделать так, чтобы макрос не реагировал, когда делаются первоначальные иземенения вне диапазона (E27;F128)

Заранее большео спасибо.

[IgorGO]

1.
в начале процедуры application.eventsenabled false
в конце application.eventsenabled true
а в самом начале еще до application.eventsenabled false я бы проверил в каком диапозоне произошли изменения, если это не подконтрольный диапазон, то тут же - выход из процедуры.

2. если первоначальные данные вносите макрос - то рецепт тот же что и для 1. - application.eventsenabled false...

[mephist]

Если я правильно понял должно быть так

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
    For Each a In Range("G27:G128").Cells
    If a.Offset(0, -1).Value > Range("f17").Cells Then
        a.Value = "Необходимость"
    End If
    Next
Application.EnableEvents = True
End Sub

Но теперь макрос вообще перестал срабатывать.
1. Можно пояснить, что делает Application.EnableEvents
2. По второй проблеме мне именно нужна проверка в каком диапазоне прозошли изменения. У меня сложность это написать на VBA.
P.S. Первоначальные изменния в диапазоне (E27;F128) вносятся нажатием клавиши F9 (выгружаются из базы данных), на это изменение макрос должен среагировать и начать заполнять диапазон G27;G128. Но макрос не должен реагировать, когда он сам же вносит изменения в G27;G128, а должен продолжать заполнять этот диапазон.

[аналитика]

Private Sub Worksheet_Change(ByVal Target As Range)
if intersect(target, Range("e27:f128")) is nothing then exit sub

Application.EnableEvents = False
    For Each a In Range("G27:G128").Cells
    If (a.Offset(0, -2).Value > Range("e17").Value) Or (a.Offset(0, -1).Value > Range("f17").Value) Then
        a.Value = "Необходимость"
    End If
    Next
Application.EnableEvents = True
End Sub

у тебя перестало работать, видимо, из-за того, что в ходе испытаний утебя отключились события (Application.EnableEvents=false), включи их (...=true)

[Hugo121]

Если по F9 - то это Private Sub Worksheet_Calculate()
Изменений на листе не будет, т.к. формулы не изменятся.
Но значения возможно будут другими.
Можно сделать так - первоначально взять подконтрольный диапазон в Public массив, при пересчёте сравнивать текущий массив с запомненным.
Если не совпадают - значит изменились, нужно действовать.

[mephist]

Оргоменное всем спасибо.
Благодаря IgorGo и аналитика получился полностью рабочий код:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("e27:f114", "e17:d17")) Is Nothing Then Exit Sub

    For Each a In Range("G27:G114").Cells
    If (a.Offset(0, -2).Value <> "") And (a.Offset(0, -1).Value <> "") Then
        If (Abs(a.Offset(0, -2).Value) > Range("d17").Value) Or (Abs(a.Offset(0, -1).Value) > Range("e17").Value) Then
            Application.EnableEvents = False
            a.Value = "Необходимость"
            Application.EnableEvents = True
        Else
            Application.EnableEvents = False
            a.Value = ""
            Application.EnableEvents = True
        End If
    End If
    Next
End Sub

Но как абсолютно верно сказал Hugo121, конечной цели я не добился, ибо по нажатию F9, формулы в диапазоне e27:f114 не меняются, а меняются только значения, соответсвенно change не срабатывает.
C Worksheet_Calculate я никогда не работал, попытался тупо заменить Worksheet_Change(ByVal Target As Range)
на Worksheet_Calculate(ByVal Target As Range), но возникает ошибка компиляции.
Подскажите, пожалуйста, как поправить код с Change на Calculate, чтобы сохранить все проверки и условия.

P.S. С Pablic массивом я тоже не знаком, потому наверное с ним разбираться придется еще дольше.

[аналитика]

в тот же модуль вставь:

Private Sub Worksheet_Calculate()
    Worksheet_Change Range("e27")
End Sub

этот кусок кода логически избыточен

        If (Abs(a.Offset(0, -2).Value) > Range("d17").Value) Or (Abs(a.Offset(0, -1).Value) > Range("e17").Value) Then
            Application.EnableEvents = False
            a.Value = "Необходимость"
            Application.EnableEvents = True
        Else
            Application.EnableEvents = False
            a.Value = ""
            Application.EnableEvents = True
        End If

причесано:

Application.EnableEvents = False
        If (Abs(a.Offset(0, -2).Value) > Range("d17").Value) Or (Abs(a.Offset(0, -1).Value) > Range("e17").Value) Then            
            a.Value = "Необходимость"            
        Else
            a.Value = ""
        End If
Application.EnableEvents = True

[mephist]

Большое-большое спасибо!
Все работает просто отлично - как раз так, как хотел.
С избыточностью кода я полностью согласен, правда, в процессе мне пришлось немного менять условия. Надеюсь, измененный код избыточностью не страдает.
В результате получился макрос:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("e26:f113", "e16:d16")) Is Nothing Then Exit Sub
    For Each a In Range("G26:G113").Cells
        Application.EnableEvents = False
        If a.Value = "Необходимость" Then a.Value = ""
        If (IsError(a.Offset(0, -1)) = False) And (IsError(a.Offset(0, -2)) = False) Then
            If (a.Offset(0, -2).Value <> "") And (a.Offset(0, -1).Value <> "") Then
                If ((Abs(a.Offset(0, -2).Value) > Range("d16").Value) Or (Abs(a.Offset(0, -1).Value) > Range("e16").Value)) And (a.Value = "") Then
                    a.Value = "Необходимость"
                End If
            End If
        End If
        Application.EnableEvents = True
    Next
End Sub
Private Sub Worksheet_Calculate()
    Worksheet_Change Range("e26:f113")
End Sub

Остался только один вопрос (в целях ЛикБеса ), как работает код?

Private Sub Worksheet_Calculate()
    Worksheet_Change Range("e26:f113")
End Sub

[Hugo121]

Я понял, что в таком варианте код по обработке этих ячеек можно было писать просто в Sub macro(), а на пересчёт прописать

Private Sub Worksheet_Calculate()
macro
End Sub

Т.к. обработка происходит при любом пересчёте, изменилось подконтрольное или нет. Так ведь, или ошибаюсь? Без примера не удостовериться...
Думаю, что сперва определить, нужно ли обрабатывать, было бы не лишним, хоть там всего 100 ячеек анализируются...

[аналитика]

Private Sub Worksheet_Change(ByVal Target As Range) - процедура обработки события изменения на листе, но тем не менее это процедура (с параметром) и получается, что при пересчете просто вызывается процедура изменения с заведомо "правильным" target'ом.
на самом деле более читабельно было бы след.:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("e26:f113", "e16:d16")) Is Nothing Then Exit Sub
    Processing
End Sub
Private Sub Worksheet_Calculate()
    Processing
End Sub

Private Sub Processing()
    For Each a In Range("G26:G113").Cells
        Application.EnableEvents = False
        If a.Value = "Необходимость" Then a.Value = ""
        If (IsError(a.Offset(0, -1)) = False) And (IsError(a.Offset(0, -2)) = False) Then
            If (a.Offset(0, -2).Value <> "") And (a.Offset(0, -1).Value <> "") Then
                If ((Abs(a.Offset(0, -2).Value) > Range("d16").Value) Or (Abs(a.Offset(0, -1).Value) > Range("e16").Value)) And (a.Value = "") Then
                    a.Value = "Необходимость"
                End If
            End If
        End If
        Application.EnableEvents = True
    Next
End Sub