Czyli coś, o czym marzy każdy użytkownik…

… no, pewnie prawie każdy :). Ja bym się nie obraziła!

Chodzi o coś takiego:

Czyli gdziekolwiek w zakresie klikniemy – ta komórka ma się podświetlać na żółto (albo oczywiście jakikolwiek inny kolor). Tylko tyle i aż tyle, ponieważ, jak zobaczycie, to wcale nie będzie takie banalne… Do stworzenia tej magii użyję nazewnictwa komórek (choć da się bez), zdarzeń w VBA (makra) i oczywiście mojego kochanego formatowania warunkowego, do którego napiszę formułę…

Formatka do zadania wygląda tak:

Formatka

1. Nazwanie komórki roboczej

Zacznijmy od nazwania komórki roboczej. Od razu powiem, że może być ona w innym, ukrytym arkuszu. Ja dałam ją w tym samym, aby czarno na białym widać było jej zmianę. Natomiast umiejscowienie jej nie ma znaczenia (tylko trzeba będzie odpowiednio się do niej odwołać w makrze).

Dla wygody nazwiemy tę komórkę np. KomorkaAdresu. Koniecznie to zrób zwłaszcza wtedy, gdy umieścisz ją w innym arkuszu – będzie to miało znaczenie dla formatowania warunkowego.

Aby nazwać komórkę – zaznacz ją, a następnie w polu nazwy (po lewej od paska formuły), wpisz nazwę: KomorkaAdresu. Zatwierdź Enterem i po sprawie.

Nazywanie komórki

2. Makro (VBA)

Kolejny etap to uzupełnianie przed chwilą nazwanej komórki.

Co powinno się w niej znajdować? Adres aktualnie zaznaczonej komórki. W jakiś sposób musimy określić, która komórka jest obecnie zaznaczona. Jakkolwiek śmiesznie to nie zabrzmi: Excel tego nie wie :). A konkretnie formuła w formatowaniu warunkowym tego nie wie. Ona umie odczytać wartość z komórki, ale nie adres aktywnej. To umie VBA. A żeby było dynamicznie zgodnie ze zmienionym zaznaczeniem, musimy użyć zdarzenia arkusza Selection Change, czyli zmiana zaznaczenia.

Aby je utworzyć, należy wejść do edytora VBA, najlepiej skrótem klawiszowym Alt + F11. Następnie w okienku Project Explorer, należy dwukrotnie kliknąć na nazwę interesującego nas Arkusza (u mnie Dane) i więc po wejściu do VBE (Visial Basic Editor), , dwa razy klikam w taki właśnie arkusz. Następnie z list rozwijanych w prawym oknie wybieramy:

(1) Worksheet, i

(2) SelectionChange (wybrane domyślnie)

Oba te kroki dokładnie opisałam tutaj.

Do procedury, która pojawiła się automatycznie wpisujemy następujący kod:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 Dim Komorka As Range
 
 Set Komorka = Range("KomorkaAdresu")
 
 Komorka.Value = ActiveCell.Address
 
End Sub

Te kilka linijek robi tyle tyle, że do komórki, którą przed chwilą nazywaliśmy wpisujemy bezwzględny adres aktywnej komórki.

3. Formatowanie warunkowe

To teraz łączymy wszystko do kupy formatowaniem warunkowym.

Najpierw jednak zastanówmy się, kiedy w ogóle komórka zakresu ma się kolorować, czyli podświetlać. Odpowiedź nasuwa się oczywista: kiedy jest aktywna. I super, dokładnie tak jest. A jeszcze dokładniej: ma się podświetlać, kiedy jej adres jest równy wartości nazwanej komórki KomorkaAdresu.

Problem jednak w tym, że nie możemy porównać sobie tak: KomorkaAdresu = A1 (gdzie A1 ma reprezentować aktywną komórkę). To będzie oznaczało bowiem porównanie wartości tych komórek. A my chcemy adresy :).

I dlatego tutaj z pomocą przychodzi funkcja ADRES, która jako swój wynik zwraca właśnie adres komórki, określonej przez wiersz i kolumnę. Jeśli więc podamy jej te dwa argumenty – otrzymamy adres komórki. Dodatkowo podamy też trzeci argument, który określi sposób adresowania – u nas bezwzględne, czyli z dwoma dolarami ($). Pomocne będą jeszcze funkcje WIERSZ, która zwraca numer wiersza komórki, wskazanej w jej argumencie, i NR.KOLUMNY, działająca analogicznie, tylko na kolumny (zwraca numer, nie literę!).

Ale to w formatowaniu warunkowym. Najpierw należy je uruchomić:

1. Zaznacz zakres, który ma być formatowany (czyli ten, w którym aktywna komórka ma się kolorować), zaczynając od pierwszej komóki tego zakresu!!!

2. Wejdź do Narzędzia główne/Formatowanie warunkowe/ Nowa reguła/ Użyj formuły do określenia komórek, które należy sformatować

3. I wpisz następującą regułę:

=ADRES(WIERSZ(B4);NR.KOLUMNY(B4);1)=KomorkaAdresu

4. Następnie kliknij przycisk Fortmatuj i wybierz formatowanie, jakie ma być zastosowane do wyróżniania aktywnej komórki, np. żółte wypełnienie.

5. Zatwierdź wszystko i gotowe!

Powyższe kroki ilustruje rysunek:

Reguła formatowania warunkowego

A efekt jest magiczny, nawet, gdy komórka miała już wcześniej kolor! Cudne, prawda? To zasługa formatowania warunkowego 🙂

Koniecznie jeszcze pamiętajcie, aby zapisać plik jako plik z obsługą makr, czyli z rozszerzeniem .xlsm (lub ewentualnie .xlsb – skoroszyt binarny).

I film z rozwiązaniem tego case’a: