Автор Тема: Выделение диапазона  (Прочитано 266 раз)

Оффлайн Посетитель 24.12.2021

  • Посетитель форума
  • Сообщений: 7
Выделение диапазона
« : 24 Декабрь 2021, 08:03 »
Товарищи добрый день! Помогите сделать динамический диапазон.

В зависимости от выбранной ячейки (на ней запускаем макрос) смещаться вниз на 4 строки и выбирать диапазон. По выбранному диапазону необходимо применить условное форматирование.

Вот мой макрос.

Макрос
Sub Критический()
'
' Критический Макрос
' ±5%
'
' Сочетание клавиш: Ctrl+й
'
 Dim diaP As Variant
    Set diaP = ActiveCell.Offset(4, 0).Range("A1:K1")
    ActiveCell.Offset(4, 0).Range("A1:K1").Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual _
        , Formula1:="WorkSheet.Average(diaP)*1,05"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent3
        .TintAndShade = 0.799981688894314
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    ActiveCell.Range("A1:K1").Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _
        Formula1:="WorkSheet.Average(diaP)*0,95"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = 0.799981688894314
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

Онлайн Администратор

  • Administrator
  • Сообщений: 1952
Re: Выделение диапазона
« Ответ #1 : 24 Декабрь 2021, 08:46 »
Пользователь устанавливает курсор-рамку в заданную ячейку и запускает макрос.
Макрос должен опуститься вниз на четыре строки и сделать в этой строке в столбцах A:K условное форматирование. Так?
Какое условное форматирование должно быть? Опишите простыми словами без использования программных и других терминов.
И выложите файл, в котором будет это условное форматирование. Сделайте его вручную.

Оффлайн Посетитель 24.12.2021

  • Посетитель форума
  • Сообщений: 7
Re: Выделение диапазона
« Ответ #2 : 24 Декабрь 2021, 09:16 »
Пользователь устанавливает курсор-рамку в заданную ячейку и запускает макрос.
Макрос должен опуститься вниз на четыре строки и сделать в этой строке в столбцах A:K условное форматирование. Так?


Все верно я ставил макрос на запись, ячейки были относительными. Но т.к. в условном форматировании в формуле используется фиксированный диапазон, потому и возникла задача поправить макрос.

условия форматирования когда мы спустились вниз на 4 строки: выделяем диапазон начальной ячейкой которого, является ячейка, которая находится в том же столбце, что и стартовая, последняя заполненная ячейка является последней ячейкой диапазона. Для значений в диапазоне определяем среднее, умножаем его на 1,05 и подкрашиваем ячейки в зелёный, которые больше. Для этого же диапазона для значений, которые меньше среднего умноженного на 0,95, окрашиваем ячейки в красный.


Онлайн Администратор

  • Administrator
  • Сообщений: 1952
Re: Выделение диапазона
« Ответ #3 : 24 Декабрь 2021, 09:43 »
Почему пользователь ставит курсор-рамку вверху, а не сразу в ту строку, в которой нужно создать условное форматирование?

Оффлайн Посетитель 24.12.2021

  • Посетитель форума
  • Сообщений: 7
Re: Выделение диапазона
« Ответ #4 : 24 Декабрь 2021, 09:51 »
Сделал так для собственного удобства когда записывал макрос, чтобы ячейки были относительные и мог применять макрос в разных частях листа.

Онлайн Администратор

  • Administrator
  • Сообщений: 1952
Re: Выделение диапазона
« Ответ #5 : 24 Декабрь 2021, 09:56 »
А вообще вам надо это так делать? Может быть вам просто ставить курсор-рамку в столбец D? И макрос вставит УФ в эту же строку в столбцы D:N.

Оффлайн Посетитель 24.12.2021

  • Посетитель форума
  • Сообщений: 7
Re: Выделение диапазона
« Ответ #6 : 24 Декабрь 2021, 10:01 »
Можно и так, главная задача это чтобы в формуле среднего значения был диапазон который выделяется - пока не могу этого добиться

Онлайн Администратор

  • Administrator
  • Сообщений: 1952
Re: Выделение диапазона
« Ответ #7 : 24 Декабрь 2021, 10:21 »
Написал вам два письма на почту с заголовком "Письмо с Форума по VBA, Excel и Word". Написал именно на почту, а не на форум.

Как запускать макрос. Поставьте курсор-рамку в любую ячейку в ту строку, в которой надо создать УФ. Не обязательно ставить курсор-рамку в столбец D.

Макрос
Sub Макрос2()
   
    Dim Rng As Range, ИсхRefStyle As Long
   
   
    ' Фрагмент, в который надо вставить УФ.
    Set Rng = ActiveCell.EntireRow.Columns("D:N")
   
    ' Запоминаем текущий стиль ссылок в формулах.
    ИсхRefStyle = Application.ReferenceStyle
   
    ' Создание первого УФ.
    If ИсхRefStyle <> xlA1 Then Application.ReferenceStyle = xlA1
    Rng.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _
        Formula1:="=СРЗНАЧА(" & Rng.Address & ")*0,95"
    With Rng.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = 0.799981688894314
    End With
    Rng.FormatConditions(1).StopIfTrue = False
   
    ' Создание второго УФ.
    Rng.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, _
        Formula1:="=СРЗНАЧА(" & Rng.Address & ")*1,05"
    With Rng.FormatConditions(2).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent3
        .TintAndShade = 0.799981688894314
    End With
    Rng.FormatConditions(2).StopIfTrue = False
   
    ' Возвращаем исходный стиль ссылок в формулах.
    Application.ReferenceStyle = ИсхRefStyle
   
End Sub

Оффлайн Посетитель 24.12.2021

  • Посетитель форума
  • Сообщений: 7
Re: Выделение диапазона
« Ответ #8 : 24 Декабрь 2021, 10:29 »
Смотрите у вас тоже получается что в формуле Formula1:="=СРЗНАЧА(RC4:RC14)*0,95" диапазон динамически не поменяется если я захочу применить УФ к другой строке, т.е. я применю форматирование к другому диапазону, но значение среднего зафиксировано для прошлого диапазона.

Онлайн Администратор

  • Administrator
  • Сообщений: 1952
Re: Выделение диапазона
« Ответ #9 : 24 Декабрь 2021, 10:30 »
Внесены изменения в ответ 7 в макрос.
Прежде чем что-то писать, нужно запускать макрос. Вы пришли на форум, а не к вам пришли. Значит вы меньше знаете. И поэтому сначала сделайте то, что вам предлагают, а потом уже пишите: получилось или нет.

Оффлайн Посетитель 24.12.2021

  • Посетитель форума
  • Сообщений: 7
Re: Выделение диапазона
« Ответ #10 : 24 Декабрь 2021, 10:53 »
Я проверял, если я захочу поменять диапазон, то Columns("D:N") меняю вот тут же?

И для чего  мы Возвращаем исходный стиль ссылок в формулах?
    Application.ReferenceStyle = ИсхRefStyle

Онлайн Администратор

  • Administrator
  • Сообщений: 1952
Re: Выделение диапазона
« Ответ #11 : 24 Декабрь 2021, 13:24 »
Я не знал, что вы проверяли, я думал, что вы просто посмотрели код. Прежде чем выкладывать код, я тестировал, у меня работала первая версия.

Онлайн Администратор

  • Administrator
  • Сообщений: 1952
Re: Выделение диапазона
« Ответ #12 : 24 Декабрь 2021, 13:26 »
Цитата:
если я захочу поменять диапазон, то Columns("D:N") меняю вот тут же?

да

Онлайн Администратор

  • Administrator
  • Сообщений: 1952
Re: Выделение диапазона
« Ответ #13 : 24 Декабрь 2021, 13:27 »
Макрос изменяет стиль ссылок на случай, если он отличается от A1. Это связано с тем, что макрос использует стиль ссылок A1, а если в Экселе будет стиль ссылок R1C1, то произойдёт run-ошибка.
После работы, макрос возвращает стиль ссылок, который был до запуска макроса.

Оффлайн Посетитель 24.12.2021

  • Посетитель форума
  • Сообщений: 7
Re: Выделение диапазона
« Ответ #14 : 24 Декабрь 2021, 13:29 »
Спасибо!!!