Страницы: 1
RSS
Заливка соседних ячеек по условию, VBA
 
Добрый день!
Помогите пожалуйста с написанием кода/формулы. разобраться не могу никак.

на листе произвольно закрашиваются ячейки, не вручную.
возможно ли как-то в модуле листа прописать формулу которая выполняла бы следующее:
если цвет какой-то ячейки изменился на, например, rgb(255;0;0), то закрашиваем тем же цветом 4 соседние справа ячейки.
только чтобы не получилось, что закрашиваться будет до бесконечности.
вот закрасилась ячейка G2 например и 4 справа тоже закрасились.
УФ тут видимо не поможет. нужен VBA.
Изменено: squadgazzz - 26.08.2014 21:14:01
 
Это достаточно трудно сделать.
Как такового события в ВБА нет. Есть похожее событие - worksheet change, но им можно воспользоваться, если меняются значения, а не форматирование ячеек. Это можно использовать в решении, которое я описываю ниже:

Если файл небольшой то можно создать вспомогательный лист, где будет храниться матрица цветов использованных ячеек, которая пересчитывается при каждом изменении цвета ячейки в исходном листе. При изменении значения соответствующего цвета в матрице будет меняться цвет ячеек по данному адресу в исходном листе.

Если интересно, могу написать макрос.

ПС На идею натолкнула похожая тема на забугорском форуме → http://www.mrexcel.com/forum/excel-questions/574405-capturing-cell-color-change-visual-basic-applications.html
С уважением,
Федор/Все_просто
 
ок. а если условие не по цвету, а по тексту?
если первая буква ячейки А, то заливаем соседние 4 ячейки в красный цвет.

с помощью УФ кстати это нельзя сделать никак? просто где появляться будет первая буква А - неизвестно.
 
Можно. У меня получилось, по крайней мере.
С уважением,
Федор/Все_просто
 
и так для каждого столбца отдельное УФ делать? а если столбцов 50? )
 
формула для УФ для диапазона А:М если нужно больше меняем диапазон в формуле на нужный.
Код
=И(СТОЛБЕЦ()>ПОИСКПОЗ("А";ЛЕВСИМВ($A1:$M1;1);0);СТОЛБЕЦ()<=ПОИСКПОЗ("А";ЛЕВСИМВ($A1:$M1;1);0)+4;ПОИСКПОЗ("А";ЛЕВСИМВ($A1:$M1;1);0)>0) 
Изменено: V - 26.08.2014 16:28:52
 
А посмотри на правило выделения повнимательнее.
Там стоит формула, которая должна быть истинной для применения форматирования. У меня стоит в данном случае для АКТИВНОЙ (подчеркиваю для активной) ячейки формула =ЛЕВСИМВ($A1)="а"
При копировании форматирования, на нужные области, они будут выделяться только и только при условии, что ячейка в первом столбце, находящаяся в этой же строке начинается на букву "а".
С уважением,
Федор/Все_просто
 
круто. работает, но не очень))
если в ячейке работает условие, то у второй ячейки этой же строки условие не срабатывает)

кстати, забыл об одной мелочи. раз теперь такой вариант, то и ячейка с А должна закрашиваться тоже.
это возможно одним условием оформить?
Изменено: squadgazzz - 26.08.2014 17:02:06
 
Может видео сделать еще?  :)
У меня кстати так и работает. Посмотри же внимательнее.
Можно скорректировать формулу, можно изменить область применения. Все получается как надо. Я в свое время настрадался с этим при работе над диаграммой Ганта (был особый случай с множеством условий). Однако теперь получил отличный инструмент в свой арсенал приемов  :)

Повторяю алгоритм действий:
1. Выделяешь нужную область (для простоты выделяй прямоугольной формы).
2. Создаешь нужное правило, где указываешь
     а. формулу
     б. форматирование.
Для пункта 2 "Главная" → "Стили" → "Условное форматирование" → "Создать правило" → "Использовать формулу для определения форматируемых ячеек"
В строке формул вписываешь формулу. Нажимаешь на формат и применяешь нужное форматирование (заливка).
Допустим нужно закрасить ячейки Е5:J5. В строке формул вписываешь
Код
=ЛЕВСИМВ($E5)="А"
С закреплением столбца Е, так что при применении правила на другие ячейки форматирование будет осуществляться с оглядкой на ячейку в столбце Е.
3. Открываешь диспетчер правил (по почти такой же цепочке). И меняешь в созданном правиле адрес в "применяется к". При сильном желании усложнить можно попробовать сделать динамический диапазон через формулу смещения. Но это уже другая история...
С уважением,
Федор/Все_просто
 
ну не работает же =ЛЕВСИМВ($E5)="А" как нужно
формулу, что предложил V - работает, но если в строке несколько раз встречается А, то срабатывает только у первого значения...
вы пишете "Допустим нужно закрасить ячейки Е5:J5"
мне нужно диапазон шире , A1:U999
к тому же закрашивать не все подряд, а только 4 ячейки справа от той, первая буква которой А.

я все пытаюсь понять как исполнить примерно такое условие:
Код
=И(ЛЕВСИМВ(а1)="А";И 4 ячейки справа)
Изменено: squadgazzz - 28.08.2014 22:26:30
 
короче не знаю как, но сработало вот так вот:
для моего диапазона
Код
=$E$3:$AF$74 
сработало вот такое условие:
Код
=ИЛИ(ЛЕВСИМВ(A3;1)="Ю";ЛЕВСИМВ(B3;1)="Ю";ЛЕВСИМВ(C3;1)="Ю";ЛЕВСИМВ(D3;1)="Ю") 
4 ячейки, включая с буквой Ю. Можно сделать и 5 и более, сместив диапазон и добавив ещё одно условие в ИЛИ.
в принципе тему можно закрывать, но мне до сих пор не понятно, как это работает))

всем спасибо за идею, без вас я бы не разобрался бы никогда.
Изменено: squadgazzz - 28.08.2014 22:26:12
 
Цитата
squadgazzz пишет:
но мне до сих пор не понятно, как это работает
если хоть одна из ячеек даст ответ ИСТИНА то вся формула даст ответ ИСТИНА что послужит сигналом закрашивания для УФ.
Если бы вы сразу указали что у вас диапазон начинается с "Е" столбца то такое решение уже давно получи ли бы.
чтобы понять сделайте следующее:
1. создайте вашу УФ для диапазона например E1:N10
2. в ячейку Е11 вставьте вашу формулу и растяните её на диапазон E11:N20
3. в диапазоне для УФ впишите "Ю" и посмотрите как сработали формула в диапазоне E11:N20 и сравните с закрашенными. Я думаю так поймете как работает УФ.
Цитата
Можно сделать и 5 и более,
ага только придется с каждым новым условием смещать диапазон "Применить к" по столбцам вправо.  ;)
Изменено: V - 27.08.2014 10:51:01
 
Да точно. приходится вставлять доп столбцы.
но с этим разберемся)
спасибо))
 
формулу кстати получилось упростить

Код
=ИЛИ(ЛЕВСИМВ(A3:D3;1)="Ю")
 
 
Доброго времени суток. Подскажите как сделать УФ  с условием - если в каком нибудь столбце четвертой строки встречается суббота или воскресенье то нужно закрасить эти столбцы с 4 строки по 30ю. спасибо
 
Igor Rezanov, некорректно день недели брать по дню без месяца и года. Формулу в 4-й строке изменил. УФ:
Код
=ЕСЛИ(ЕЧИСЛО(C$5);ДЕНЬНЕД(C$5;2)>5)
Изменено: JayBhagavan - 26.11.2014 22:52:42

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
Скажите, а для чего здесь проверка ЕЧИСЛО(C$5), ведь можно оставить только вторую часть формулы (ДЕНЬНЕД(C$5;2)>5))?
 
openid.mail.ru/mail/irez, измЕните отображаемое имя - скажу. Хотя кто/что Вам мешает проверить и понять?

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
извиняюсь, я просто пароль забыл и вошел через mail.ru
дык я и хочу понять в чем может скрываться проблема, а пока всё работает и кажется все нормально. наверно мастерство и заключается в том - чтобы видеть все подводные камни на которые потом можно напороться.
кстати - спасибо  ;)  все работает отлично
 
Igor Rezanov, если вместо числа будет "", то будет ошибка. Потому проверка на число.

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
дык в С5 макросом проставляется дата (текущий месяц и прошлый месяц), а если ввести какие то данные в с5 вручную то меняется и весь календарь. кстати - можно ли какнить зщитить ячейку с5 от ввода вручную? если защитить стандартным методом то перестает работать макрос
 
Igor Rezanov, форма у Вас фиксированная на 31 день, если в месяце меньше дней, то лишние дни забиваются "". Уберите проверку и посмотрите на результат. По все остальным вопросам читайте форум и, если не найдёте ответа, то создавайте нов. тему.

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
Страницы: 1
Наверх