Подсветите просроченные задачи автоматически: Excel сам покажет, где сроки уже горят. Подробно разбираем, как автоматически подсвечивать просроченные задачи в Excel с помощью VBA. Один макрос помогает быстро находить задачи с истёкшим сроком, выделять важные строки и не упускать дедлайны. Готовый код, разбор строк, реальные сценарии и способы адаптации под свою таблицу.
Когда дедлайны тонут в таблице, проблема уже не в дисциплине
Есть очень типичная история. В начале всё идёт ровно. Таблица аккуратная, задач немного, сроки видны, статусы понятны. Кажется, что всё под контролем. Но потом задач становится больше. Кто-то добавляет новые строки. Кто-то меняет ответственного. Кто-то переносит срок. Кто-то забывает обновить статус. И постепенно таблица из рабочего инструмента превращается в длинную ленту, где важное и второстепенное стоят рядом, как пассажиры в очереди на маршрутку — вроде все на месте, но кто тут срочный, а кто просто ждёт, уже не понять.
И вот в такой таблице обычно рождается самая дорогая ошибка. Не формула. Не сломанный макрос. Не удалённый лист. А пропущенный дедлайн.
Причём самое неприятное в этом то, что просроченная задача почти никогда не выглядит драматично. Она просто стоит где-то в середине списка. Вчера срок был актуальный, сегодня уже нет, а визуально строка выглядит почти так же. Если пользователь не сортировал, не фильтровал и просто быстро пробежался глазами — задача легко прячется. Excel тут ни при чём. Он честно показывает данные. Просто человеку в длинной таблице тяжело каждый раз играть в инспектора сроков.
Вот здесь и начинается по-настоящему взрослая автоматизация. Не та, где «смотрите, как красиво код пишет дату», а та, где файл реально спасает от рабочих потерь.
Почему ручной контроль сроков почти всегда даёт сбой
Многие думают, что просроченные задачи можно заметить и так. Глазами. Особенно если таблица знакомая. Теоретически — да. Практически — только пока строк немного.
Когда задач десять, всё видно.
Когда их тридцать, уже хуже.
Когда шестьдесят — начинаются пропуски.
Когда сто двадцать — вы уже не проверяете, а надеетесь.
А надежда, как известно, плохой инструмент для контроля сроков.
Можно, конечно, периодически сортировать по дате. Можно включать фильтры. Можно ставить условное форматирование вручную. Но в живой работе всё, что требует отдельного ритуала, со временем начинают делать реже. А потом и вовсе перестают. Потому что день идёт, сообщения летят, задачи прилетают сверху, и мало кто мечтает в 16:47 вручную перепроверять, не стал ли срок в строке 83 вчерашним.
Значит, нужен не совет, а автоматический сигнал. Такой, который сам бросается в глаза.
Где этот сценарий особенно полезен
Сценарий 1. Отдел продаж, сопровождение клиентов, проектная работа
Есть таблица с клиентами, задачами, сроками и статусами. В ней ведутся звонки, коммерческие предложения, оплаты, согласования, отправка документов. Если срок по задаче прошёл, но статус ещё не «Завершено», это уже красный флаг. Макрос подсвечивает такие строки, и руководитель или сотрудник видит проблему сразу, а не через три дня после неудобного вопроса.
Сценарий 2. Склад, логистика, сервис, выездные работы
Есть журнал заявок, доставок, отгрузок, ремонтов, монтажей. Там дата заявки, срок выполнения, исполнитель, комментарий, статус. Если задача просрочена, а строка визуально не выделяется, её легко потерять в потоке. Макрос делает так, чтобы просрочка сама выходила на сцену, а не сидела тихо в массовке.
Сценарий 3. Бухгалтерия и административные процессы
Оплаты, акты, закрывающие документы, отчёты, согласования. Везде есть сроки. И везде одно и то же: задача ещё числится в работе, а дедлайн уже уехал в прошлое. Подсветка здесь работает не как украшение, а как страховка.
Именно такие практичные сценарии обычно сильнее всего заходят по сохранениям. Потому что читатель мгновенно понимает: это не «интересный макрос», это щит от лишних проблем.
Формулы могут подсказать, но макрос делает контроль живым
Да, часть задачи можно решить формулой. Например, можно добавить служебный столбец и написать:
=ЕСЛИ(И(F2<>"Завершено";E2<СЕГОДНЯ());"Просрочено";"ОК")
Формула покажет, что срок прошёл, если статус ещё не завершён. Это полезно. Но у такого подхода есть ограничения.
Во-первых, нужен отдельный столбец.
Во-вторых, кто-то должен смотреть именно на него.
В-третьих, таблица от этого не становится визуально понятной.
В-четвёртых, такие служебные столбцы быстро размножаются, как старые папки на рабочем столе: сначала одна, потом две, потом уже сам боишься их трогать.
Макрос в этом сценарии хорош тем, что действует прямо по месту. Не пишет «возможно, тут проблема», а сразу окрашивает строку или ячейку так, что задача становится видимой.
Что мы хотим получить в итоге
Предположим, у нас есть лист Данные со структурой:
A — Дата
B — Клиент
C — Задача
D — Ответственный
E — Срок
F — Статус
G — Комментарий
Логика будет такой:
- макрос находит последнюю строку;
- снимает старую подсветку;
- проходит по всем строкам;
- смотрит на срок в столбце E;
- смотрит на статус в столбце F;
- если срок меньше текущей даты и статус не равен «Завершено», подсвечивает строку;
- в конце сообщает, сколько просроченных задач найдено.
То есть Excel не просто хранит дедлайны, а сам сигнализирует, где работа уже вывалилась за срок.
Готовый VBA-код для подсветки просроченных задач
Ниже — рабочий макрос. Он проверяет таблицу на листе Данные, ищет просроченные задачи и подсвечивает всю строку целиком.
Sub ПодсветитьПросроченныеЗадачи()
Dim ws As Worksheet*
Dim lastRow As Long*
Dim i As Long*
Dim overdueCount As Long*
Dim taskDate As Variant*
Dim taskStatus As String*
Set ws = ThisWorkbook.Worksheets("Данные")*
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row*
ws.Range("A4:G" & lastRow).Interior.ColorIndex = xlNone*
overdueCount = 0*
For i = 4 To lastRow*
taskDate = ws.Cells(i, 5).Value*
taskStatus = Trim(ws.Cells(i, 6).Value)*
If IsDate(taskDate) Then*
If CDate(taskDate) < Date And LCase(taskStatus) <> LCase("Завершено") Then*
ws.Range(ws.Cells(i, 1), ws.Cells(i, 7)).Interior.Color = RGB(255, 199, 206)*
overdueCount = overdueCount + 1*
End If*
End If*
Next i*
If overdueCount = 0 Then*
MsgBox "Просроченных задач не найдено.", vbInformation*
Else*
MsgBox "Найдено просроченных задач: " & overdueCount, vbExclamation*
End If*End Sub
Как работает этот макрос — подробно, без лишней мистики
Вот теперь разберём его по частям. В таких местах у читателя обычно и возникает настоящее ощущение роста: он не просто копирует готовое решение, а понимает, из каких досок сколочен этот рабочий ящик.
1. Объявляем переменные
Сначала макрос создаёт переменные:
ws — лист с таблицей;
lastRow — последняя строка;
i — счётчик цикла;
overdueCount — количество просроченных задач;
taskDate — срок задачи;
taskStatus — статус задачи.
Это обычная подготовка. Без неё код был бы как инструментальный ящик, куда всё свалили без разделителей.
2. Указываем лист с данными
Set ws = ThisWorkbook.Worksheets("Данные")
Если ваш лист называется иначе — Задачи, Журнал, Проекты, План, — меняете только название. Логика не ломается.
3. Определяем последнюю строку
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
Макрос идёт снизу вверх по столбцу A и находит последнюю заполненную строку. Это позволяет работать с таблицей любой длины — от 15 строк до 500. Ручная жёсткая привязка к диапазону здесь не нужна.
4. Снимаем старую подсветку
ws.Range("A4:G" & lastRow).Interior.ColorIndex = xlNone
Очень важный момент.
Перед новой проверкой макрос очищает старую заливку. Иначе получится классическая картина: часть задач уже исправили, срок перенесли или статус поменяли, а красный цвет остался, как плохая слава после прошлой недели.
Обрати внимание: очищается не весь лист, а только рабочий диапазон таблицы. Это разумный подход — не трогаем то, что не связано с задачами.
5. Обнуляем счётчик просрочек
overdueCount = 0
С этого начинается новая проверка. Каждый запуск макроса должен давать свежий итог, а не тащить старые цифры.
6. Проходим по всем строкам с данными
For i = 4 To lastRow
Почему с 4 строки? Потому что предполагается, что заголовки или служебная шапка выше. Если в вашей таблице данные начинаются со 2 строки, это число нужно изменить. Именно такие мелочи и важно понимать, а не вставлять код слепо.
7. Берём срок и статус из текущей строки
taskDate = ws.Cells(i, 5).ValuetaskStatus = Trim(ws.Cells(i, 6).Value)
Пятый столбец — это срок. Шестой — статус.
Trim убирает лишние пробелы. Это полезно, потому что в реальных таблицах статус иногда выглядит как «Завершено », а визуально это не видно. Excel же видит всё и может начать вредничать, если ему оставить лишний хвост.
8. Проверяем, что в ячейке действительно дата
If IsDate(taskDate) Then
Эта строка спасает от ненужных ошибок. Если в ячейке пусто, текст или что-то странное, макрос не будет пытаться насильно сделать из этого дату.
Это хороший рабочий принцип: сначала проверить, потом сравнивать.
9. Сравниваем срок с текущей датой и смотрим на статус
If CDate(taskDate) < Date And LCase(taskStatus) <> LCase("Завершено") Then
Вот это сердце сценария.
CDate(taskDate) < Date — срок уже раньше сегодняшнего дня.
LCase(taskStatus) <> LCase("Завершено") — статус не «Завершено».
То есть задача считается просроченной только если она должна была быть сделана раньше, но по факту ещё не закрыта.
LCase нужен, чтобы не зависеть от регистра. Пользователь может написать «завершено», «Завершено» или даже «ЗАВЕРШЕНО». Макрос всё равно поймёт, о чём речь.
10. Подсвечиваем всю строку
ws.Range(ws.Cells(i, 1), ws.Cells(i, 7)).Interior.Color = RGB(255, 199, 206)
Если задача просрочена, макрос красит весь диапазон от A до G в этой строке.
Почему целую строку, а не только срок? Потому что в работе важнее видеть задачу как единый объект: клиент, описание, ответственный, срок, статус. Если красить только одну ячейку, сигнал слабее. Если всю строку — проблема сразу читается.
11. Увеличиваем счётчик
overdueCount = overdueCount + 1
Каждая найденная просроченная задача увеличивает общий счётчик. Это нужно, чтобы в конце показать понятный итог.
12. Сообщаем результат
If overdueCount = 0 Then
MsgBox "Просроченных задач не найдено.", vbInformation*Else
MsgBox "Найдено просроченных задач: " & overdueCount, vbExclamation*End If
Это уже финальная точка. Макрос не просто делает работу, но и коротко подводит итог. Очень по-человечески и удобно.
Как изменить этот макрос под свою таблицу
Вот здесь начинается самое полезное.
Если срок стоит в другом столбце
Сейчас срок берётся так:
taskDate = ws.Cells(i, 5).Value
Это столбец E.
Если срок у вас в столбце G, ставьте 7:
taskDate = ws.Cells(i, 7).Value
Если статус в другом столбце
Сейчас:
taskStatus = Trim(ws.Cells(i, 6).Value)
Это столбец F.
Если статус в H, ставьте 8.
Если данные начинаются с другой строки
Сейчас цикл начинается с 4 строки:
For i = 4 To lastRow
Если таблица начинается со 2 строки, меняете 4 на 2.
Если нужно подсвечивать только ячейку срока
Тогда вместо всей строки используйте только столбец E:
ws.Cells(i, 5).Interior.Color = RGB(255, 199, 206)
Но честно говоря, для реальной работы это слабее. Целая строка читается лучше.
Если задача считается просроченной только при конкретных статусах
Например, нужно подсвечивать только те строки, где статус «В работе» или «Новая», но не трогать «Отложено». Тогда условие можно расширить. Вот такой вариант:
If CDate(taskDate) < Date And (LCase(taskStatus) = LCase("В работе") Or LCase(taskStatus) = LCase("Новая")) Then
Это уже ближе к бизнес-логике, а не к универсальному шаблону.
Второй рабочий сценарий: не просто подсветить, а выделить срочность цветами
Теперь покажу более взрослый и интересный вариант. Иногда одной красной подсветки мало. Хочется различать уровни риска:
- если срок прошёл — красный;
если срок сегодня — жёлтый;
если срок завтра — светло-оранжевый или другой заметный оттенок.
Так пользователь видит не только уже горящие задачи, но и те, что начинают дымиться. Вот рабочий макрос для такой логики.
Sub ПодсветитьСрокиПоПриоритету()
Dim ws As Worksheet*
Dim lastRow As Long*
Dim i As Long*
Dim taskDate As Variant*
Dim taskStatus As String*
Set ws = ThisWorkbook.Worksheets("Данные")*
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row*
ws.Range("A4:G" & lastRow).Interior.ColorIndex = xlNone*
For i = 4 To lastRow*
taskDate = ws.Cells(i, 5).Value*
taskStatus = Trim(ws.Cells(i, 6).Value)*
If IsDate(taskDate) And LCase(taskStatus) <> LCase("Завершено") Then*
If CDate(taskDate) < Date Then*
ws.Range(ws.Cells(i, 1), ws.Cells(i, 7)).Interior.Color = RGB(255, 199, 206)*
ElseIf CDate(taskDate) = Date Then*
ws.Range(ws.Cells(i, 1), ws.Cells(i, 7)).Interior.Color = RGB(255, 235, 156)*
ElseIf CDate(taskDate) = Date + 1 Then*
ws.Range(ws.Cells(i, 1), ws.Cells(i, 7)).Interior.Color = RGB(255, 242, 204)*
End If*
End If*
Next i*
MsgBox "Проверка сроков завершена.", vbInformation*End Sub
Этот вариант уже гораздо ближе к реальной диспетчерской логике. Не только тушим пожар, но и видим, где скоро начнёт припекать.
Где ещё использовать такую механику
Подсветка сроков работает не только для задач.
Её можно использовать для:
- сроков оплат;
- дат отгрузки;
- окончания договоров;
- контроля интервью и следующих этапов найма;
- дедлайнов по документам;
- сдачи проектов;
- возврата оборудования;
- согласования актов;
- проверки лицензий, сертификатов, доступов.
То есть этот макрос — не про одну конкретную таблицу. Он про сам принцип: Excel может не просто хранить срок, а визуально кричать, когда этот срок уже пошёл под откос.
Маленькая история, которую многие узнают без подсказок
Обычно просроченная задача становится проблемой не в день просрочки. А позже. Когда кто-то пишет: «Что с этим?», и начинается раскопка причин. Потом открывают таблицу, видят строку, срок в ней был ещё три дня назад, но никто не заметил, потому что она выглядела так же спокойно, как и все остальные.
И вот на этом месте становится понятно: проблема была не в сотруднике, не в таблице и не в Excel. Проблема была в отсутствии сигнала.
Макрос тут не творит чудес. Он не выполняет задачу сам. Но он делает главное — не даёт просрочке сидеть тихо и незаметно. А это уже очень много.
Как связать 6 часть с предыдущими материалами
В 3 части мы проверяли заполненность таблицы. Это контроль полноты данных.
В 4 части создавали листы по шаблону. Это порядок структуры.
В 5 части сортировали данные одной кнопкой. Это порядок внутри таблицы.
А 6 часть — это уже визуальный контроль по срокам. То есть таблица не просто заполнена и отсортирована, а начинает сама показывать, где проблемы.
Вывод
Автоматизация в Excel становится действительно полезной не тогда, когда код выглядит сложно, а тогда, когда он начинает выполнять роль внимательного помощника. Подсветка просроченных задач — как раз такой сценарий. Она не делает работу за человека, но не даёт важным вещам тихо утонуть в таблице.
А это и есть настоящая практическая ценность VBA: убрать рутину, усилить контроль и сделать так, чтобы файл начал предупреждать о проблемах раньше, чем они станут заметны по последствиям.
Сохраните статью, если дедлайны в ваших таблицах иногда теряются среди остальных строк. А файл с готовым примером и двумя версиями макроса забирайте в Telegram — там будет удобная основа, которую можно подстроить под задачи, клиентов, оплаты, документы и любой рабочий журнал.
Подписывайтесь на канал, если хотите собрать Excel-автоматизацию как цельную систему. В следующей части разберём ещё один очень сильный рабочий сценарий: как формировать список уникальных значений через VBA, чтобы Excel сам собирал чистые перечни клиентов, сотрудников, проектов или статусов без дублей.