Як підсвітити рядок і стовпець активної комірки (Автоматична заливка комірок Excel)

Інколи може знадобитися виділити кольором весь рядок і колонку активної комірки таблиці Excel, щоб було зручніше аналізувати дані.

Підсвічування рядка і стовпця активної комірки за допомогою умовного форматування

Для того щоб це зробити виконайте наступні дії:

1. Виділіть усі комірки на Аркуші книги Excel, зробивши клік по кнопці Виділити все в лівому верхньому куті аркуша, або натиснувши Ctrl+A.
2. На Стрічці перейдіть у вкладку Основне і в блоці команд Стилі оберіть Умовне форматування → Створити правило…
3. У вікні що з’явилося виберіть Використовувати формулу для визначення клітинок для форматування.
4. В поле Форматувати значення, для яких ця формула має значення ІСТИНА введіть =ROW()=CELL("ROW").
5. Натисніть на кнопку Форматувати…
6. У вікні що з’явилося перейдіть на вкладку Заливка і оберіть колір заливки для виділення рядка.
7. Натисніть OK.
8. Натисніть OK.

Це призведе до того що в нас буде змінюватися заливка всіх клітинок в тому рядку де знаходиться активована комірка. Але поки що це робиться не автоматично.

Тепер потрібно зробити так, щоб виділявся стовпець в якому знаходиться активна комірка.

Для цього виконайте наступні дії:

1. Виділіть усі комірки на Аркуші книги Excel, зробивши клік по кнопці Виділити все в лівому верхньому куті аркуша, або натиснувши Ctrl+A.
2. На Стрічці перейдіть у вкладку Основне і в блоці команд Стилі оберіть Умовне форматування → Керувати правилами…
3. У вікні що з’явилося натисніть Створити правило…
4. У вікні що з’явилося виберіть Використовувати формулу для визначення клітинок для форматування.
5. В поле Форматувати значення, для яких ця формула має значення ІСТИНА введіть =COLUMN()=CELL("COL").
6. Натисніть на кнопку Форматувати…
7. У вікні що з’явилося перейдіть на вкладку Заливка і оберіть колір заливки для виділення стовпця.
8. Натисніть OK.
9. Натисніть OK.
10. Натисніть OK.

Тепер у нас змінюватися заливка всіх клітинок в тому стовпці, де знаходиться активована комірка. Але знову ж таки, поки що це відбувається не автоматично.

Тому:

1. Зробіть клік правою кнопкою миші по назві аркуша (в моєму випадку це Аркуш1) і у меню що з’явилося оберіть Перегляд коду. Або натисніть Alt+F11.
2. Переконайтеся в тому, що у вікні Проектів VBA активований саме той аркуш на якому ви до цього створювали правила.
3. У вікні редагування коду VBA зі списку об’єктів оберіть Worksheet.
4. Переконайтеся в тому що у вікні була створена процедура Private Sub Worksheet_SelectionChange(ByVal Target As Range).
5. Якщо у вас була створена інша процедура то зі списку процедур і подій оберіть SelectionChange. Але зазвичай ця процедура обирається за замовчуванням.
6. В тілі процедури напишіть наступний код: Target.Calculate

Цим ми кодом ми робимо так, щоб кожного разу, коли користувач вибирає іншу комірку на аркуші, викликався метод Calculate для цієї комірки і таким чином відбувалося оновлення значень формул або виконання певних дій, пов'язаних зі зміною вибору користувача.

Збережіть проект і збережіть Книгу Excel з підтримкою макросів.

Тепер, коли ви будете активувати будь яку клітинку на аркуші, то по горизонталі і вертикалі заливка комірок буде змінюватися на ту яку ви раніше встановили в правилах.

Код VBA для підсвічування рядка та стовпця активної комірки

Нашу задачу можна реалізувати і без Умовного форматування, за допомогою VBA.

Для цього:

1. Зробіть клік правою кнопкою миші по назві аркуша (в моєму випадку це Аркуш1) і у меню що з’явилося оберіть Перегляд коду. Або натисніть Alt+F11.
2. Переконайтеся в тому, що у вікні Проектів VBA активований саме той аркуш який ви обрали.
3. Вставте у вікно редагування коду VBA наступний код:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ' Скидання попереднього виділення рядка і стовпця
    Cells.Interior.ColorIndex = 0
    
    ' Перевірка, що вибрано комірку у межах таблиці (не заголовок)
    If Target.Row > 1 And Target.Column > 1 Then
        ' Виділення всього рядка та стовпця, в яких знаходиться обрана комірка
        Rows(Target.Row).Interior.Color = RGB(0,191,255)  ' Колір для рядка
        Columns(Target.Column).Interior.Color = RGB(0,191,255)  ' Колір для стовпця
    End If
End Sub

Відео як виконати заливку рядка і стовпця активної комірки