fbpx

Wyróżnianie aktywnej komórki kolorem

18.04.2018 | ECP3, Formatowanie warunkowe, Makra VBA

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

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

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

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 na koniec, jak zwykle, plik z gotowcem do pobrania:
MalinowyExcel Aktywna komórka podświetlona dw.xlsm

I film z rozwiązaniem tego case’a:

 

 

Jeżeli chcesz lepiej poznać makra i VBA zapisz się na listę zainteresowanych kursem Excel w codziennej pracy cz. 3! Podczas oczekiwania na kurs będę Ci wysyłała informacje, ciekawostki i excelowe triki. Bezpłatnie! 

Pomogłam Ci? Postaw mi wirtualną kawę! →

Przeczytaj podobne wpisy

Kategorie

19 komentarzy

  1. Zadanie dla trenerki: zrobić to samo bez punktu 1 – nazywania komórki roboczej.

    Reply
    • 🙂

      Reply
  2. Wszystko działa poprawnie, tylko po wpisaniu wartości w komórkę i przejściu do innej komórki nie da się użyć opcji cofnij. Czy da się coś z tym zrobić?
    Dziękuję

    Reply
    • Hmmm…. jest to związane z tym,że działania makr nie da się cofnąć. Można to oszukać tak: zatwierdź wpis do komórki kombinacją Ctrl + Enter. Wtedy nie zmienisz zaznaczenia komórki i zadziała 🙂

      Reply
  3. Może zna ktoś sposób na pominięcie formatowania aktywnego wiersza w przypadku drukowania?

    Reply
    • Hej, może za pomocą VBA ustawić się w komórce poza obszarem wydruku i obszarem formatowania warunkowego zarazem? Albo bez VBA ręcznie :).

      Reply
      • Ręcznie ustawienie kursora poza obszarem formatowania warunkowego nie jest rozwiązaniem – chyba, że taki arkusz tworzymy tylko dla swoich potrzeb. Za pomocą VBA hmn w takim przypadku trzeba by zrobić specjalny przycisk do drukowania, a to również słabe rozwiązanie.

        Reply
        • Ok, rozumiem. Tak do VBA warto byłoby zrobić przycisk.

          Reply
  4. Witam, moje pytanie dotyczy rozszerzenia formatowania aktywnej komórki na wiersz z aktywną komórką. W jaki sposób zmodyfikować formułę, żeby formatowanie warunkowe nadało kolor na cały wiersz lub jego część – np w 10 kolejnych kolumnach?

    Reply
    • Trzeba wstawić $ przy kolumnie, w formule formatowania warunkowego 🙂

      Reply
  5. Bardzo przydatne rozwiązanie. Mam pytanie jak to zrobić na dziesięciu arkuszach tego samego skoroszytu ?

    Reply
    • Hej, trzeba byłoby to zrobić dla każdego arkusza ;(

      Reply
  6. Bardzo interesujące rozwiązanie. ALE (bo zawsze jest jakieś „ale”), jak zrobić to dla więcej niż jednej aktywnej komórki?

    Reply
  7. Myślałem, że lepsze zaznaczenie aktywnej komórki (a numeru wiersza i kolumny też) jest po prostu opcją w excelu… No to bieda.

    Reply
    • Ech niestety nie… fakt – bieda 🙂

      Reply
  8. Dzień dobry. Na początku wpisu napisała Pani, że możemy to zrobić w ukrytym arkuszu.
    Co trzeba dopisać do formuły „adres”, żeby wyrzucało aktywną komórkę z innego arkusza?

    Reply
  9. Witam
    A jaki byłby kod gdzie zaznaczona komórka podświetla cały wiersz?
    Dzieki za odpowiedz

    Reply
  10. Witam serdecznie:

    Proszę o pomoc odnośnie tego tematu. Po uruchomieniu tych funkcji i zapisaniu ich na arkuszu z obsługą makr po ponownym otworzeniu funkcja nie działa. wszystkie formuły i makro są wpisane ale nie działa makro i nie można go uruchomić.

    Reply

Submit a Comment

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *