Formatowanie warunkowe i wyróżnianie aktywnego wiersza

by Marcin

Formatowanie warunkowe i wyróżnianie aktywnego wierszaOd czasu do czasu przydaje się możliwość wyróżnienia (podświetlenia) aktywnego wiersza lub kolumny. Dzisiaj napiszę o bardzo prostej metodzie pozwalającej osiągnąć ten efekt. Wykorzystamy formatowanie warunkowe oraz kilka linijek kodu VBA.


Doskonale wiesz, że korzystając z formatowania warunkowego możesz zmienić format (wyróżnić) komórki spełniające określone kryteria. W naszym przypadku, zaznaczone muszą zostać tylko te komórki, które leżą w wierszu, w którym znajduje się aktywna komórka. Nasze, przykładowe, dane wyglądać będą tak, jak na rysunku poniżej.

Formatowanie warunkowe i wyróżnianie aktywnego wiersza

Zaznacz interesujący Cię zakres komórek i wybierz z menu Formatowanie warunkowe. Jako warunek wybierz „formuła jest” i wprowadź następującą formułę.

= WIERSZ(C9)=aktywnywiersz

Formatowanie warunkowe i wyróżnianie aktywnego wiersza

Uwaga: Adres komórki po lewej stronie formuły (u mnie C9) musi odpowiadać AKTYWNEJ KOMÓRCE zaznaczonego zakresu. Spójrz na rysunek poniżej. Widać na nim, że w zaznaczonym obszarze jedna komórka (jeżeli nic po drodze nie „namieszasz” będzie to komórka w lewym górnym rogu zakresu) nie jest pokolorowana (nie ma niebieskiego tła). Ta właśnie komórka jest komórką aktywną i adresu tej właśnie komórki powinieneś użyć w formatowaniu warunkowym. Jak doskonale widać, w naszym przykładzie, jest to komórka C9.

Formatowanie warunkowe i wyróżnianie aktywnego wiersza

Zapis WIERSZ(C9)=aktywnywiersz oznacza mniej więcej tyle, że komórka wybranego zakresu zostanie sformatowana jeżeli numer wiersza tej komórki równy jest wartości przechowywanej w zdefiniowanej nazwie aktywnywiersz.

To, co teraz musisz zrobić to zdefiniować tę nazwę i znaleźć sposób w jaki przypisywać jej numer wiersza aktywnej komórki. Na początek zdefiniuj więc nową nazwę i nadaj jej, na początek, po prostu wartość zero.

Formatowanie warunkowe i wyróżnianie aktywnego wiersza

Na koniec, musisz w jakiś sposób zmieniać, w zależności o wiersza, w którym znajduje się aktywna komórka, wartość przypisaną tej nazwie. Wykorzystamy w w tym celu VBA.

Uruchom edytor VBA i dla arkusza, w którym chcesz podświetlać wiersze wprowadź kod:


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveWorkbook.Names("AktywnyWiersz").RefersTo = "=" & ActiveCell.Row
End Sub

Jeżeli chciałbyś, aby komórki podświetlane były tylko wtedy kiedy poruszasz się w ramach swojego zakresu możesz zmienić nieco kod, który mógłby wyglądać następująco (dla naszego przykładu):


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("C9:L30")) Is Nothing Then
ActiveWorkbook.Names("AktywnyWiersz").RefersTo = "=" & ActiveCell.Row
End If
End Sub

Efekt docelowy wygląda mniej więcej tak:

Formatowanie warunkowe i wyróżnianie aktywnego wiersza

Marcin

Download

{ 37 comments… read them below or add one }

kermit

W załączniku zamiast pliku xls jest dokument z artykułem.

Marcin

Uwaga bardzo trafna :) Dziękuję. Plik już podmieniony.

Marcin

m.gr

Bardzo fajne. Aczy można by to zrobić tak by podświetlany był wiersz w zależności od zawartości komórki. np. jeżeli w kom. c5 istnieje wpis „E 20/50″ wtedy wiersz będzie podświetlony. Najlepiej by było gdyby to działało w zależności od fragmentu tekstu Np. komórka zawiera „grz 500″ inna „al 500″ a ja chciałby by podświetlona była ta która zawiera fragment „grz”

Pozdrawaiam Marek Groblicki

Marcin

Rozwiązań byłoby pewnie kilka. Możesz popróbować na przykłąd jakoś tak:

Zaznacz obszar, w którym chcesz, aby wiersze sie podświetlały(u mnie np. A1:F20). Następnie wybierz formatowanie warunkowe. Załóżmy, że w kolumnie C masz wpisy, które chcesz sprawdzać i na ich podstawie podświetlać (albo i nie) wiersze. Formuła do użycia w formatowaniu warunkowym mogłaby wyglądać na przykład tak:

=NIE(CZY.BŁĄD(SZUKAJ.TEKST(„*grz*”;$C1)))

Powinno działać :)

Marcin

karimmo

Witam, Bardzo fajne i proste rozwiązanie (takie lubię najbardziej) – nie wpadłem do tej pory na takie wykorzystanie „nazw” – mogłoby się wydawać, że oczywiste!!! Na pewno w jakiś sposób wykorzystam – więc dzięki.
Pozdrawiam i zabieram się do dalszej eksploracji Twojego bloga.
Karimmo

ALTAIR

Fajna propozycja, chciałem zastosować ale u mnie jest problem którego nie potrafię usunąć:
Mam tabelę, grupa(A8:a255),nazwa(b8:b255),cena netto(c8:c255),vat(d8:d255),brutto(e8:e255).
Do tej pory po kliknięciu w kolumnie b, wyświetlało mi się zdjęcie produktu:

‚Wyświetlenie zdjęcia
If Target.Column = 2 And Target.Row > 7 Then
plik = „c:\baza\kompletacja\foto\” & Cells(Target.Row, 13) [w kolumnie m jest nazwa pliku ze zdjęciem]
With UserForm1
.Caption = Target.Value ‚nazwa okienka
.Picture = LoadPicture(plik) ‚plik w okienku
.PictureSizeMode = fmPictureSizeModeZoom
.Show
Cells(Target.Row + 1, 2).Select
End With
End If

po dodaniu:
‚podświetlenie wiersza
‚Private Sub Worksheet_SelectionChange(ByVal Target As Range)
‚If Not Intersect(Target, Range(„a8:e255″)) Is Nothing Then
‚ActiveWorkbook.Names(„AktywnyWiersz”).RefersTo = „=” & ActiveCell.Row
‚End If
i kliknięciu w kolumnie b z nazwami produktów przelatuje całą bazę i po kolei wyświetla zdjęcia.

na początku mam:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim plik As String

Jak sprawić, aby wiersz się wyświetlał na zielono i jednocześnie żeby nie wyświetlał zdjęcia ?
podejrzewam konflikt BeforeDoubleClick z SelectionChange

margerytka

Witam!

Mam pytanko. Nigdzie tego wcześniej nie widziałam. Jak się robi tak, jak zrobiłeś w pliku z przykładem, że jest widoczny tylko zakres A1:N38, a reszta kolumn i wierszy jest wyszarzona?

Pozdrowienia

Margerytka

Marcin

@margerytka

Sugeruję poczytać o ukrywaniu kolumn i wierszy :)

Marcin

margerytka

Wiedza tajemna, o której piszesz jest mi znana :))) Tylko nie wiedziałam, że można ukryć do końca wiersze i kolumny i że taki efekt można w ten sposób uzyskać. Nie sądziłam, że to takie proste ;)

Bogdan

Dziękuję za ten bardzo przydatny mi trik z podświetleniem wiersza. Utworzyłem potrzebny mi arkusz w Excelu 2003 – tam wszystko było OK, ale niestety w pracy muszę pracować na OpenOffice 2.4.1 i tam to makro nie chce działać – openoffice ma inną składnię, czy też inne nazwy poleceń. Czy mógłbym prosić o pomoc i „przetłumaczenie” tego makra tak, by działało. Ja nie znam się kompletnie na VB, i niestety problem mnie przerasta, a od bardzo dawna szukałem czegoś takiego :(

Serdecznie pozdrawiam i proszę o pomoc – jeśli to możliwe.

Marcin

@ Bogdan

Cieszę się, że opisywane przeze mnie rzeczy znajdują zastosowanie w Twojej pracy. Bardzo chętnie bym Ci pomógł, ale niestety kompletnie nie znam się na OpenOffice. Popytaj na którejś z grup dyskusyjnych poświęconych właśnie temu programowi.

Marcin

Bogdan

Spróbowałem pomocy na forum OpenOffice.pl,, ale jak na razie nikt mi nie pomógł. Dziękuję za podanie pomysłu, jak poradzić sobie z problemem.

grzechuuuu

Fajny trik.
Jedna uwaga – przeliczanie arkusza musi być ustawione na Automatyczne

Pozdrawiam

t0shUT

przyda sie, fajna stronka pozdr

PaK

A co zrobić żeby również była wyróżniona (podświetlona) aktywna kolumna?

Pozdrawiam

menart

Marcin ta Twoja recepturka „=NIE(CZY.BŁĄD(SZUKAJ.TEKST(”*grz*”;$C1)))” jest po prostu genialna. Bardzo mi się przydała.
Wielkie dzięki.
Pozdrawiam

alex

A czy da się zmienić kolor podświetlenia? Bo taki szary jest prawie niewidoczny…
I czy da się zrobić tak, żeby podświetlana była tylko aktywna komórka?

Grygolas

Bardzo przydatna funkcja ta “=NIE(CZY.BŁĄD(SZUKAJ.TEKST(”*grz*”;$C1)))”
Już mi śmiga w raporcie :)

Jerry

Panowie i Panie..

A ja mam dla Was zagadkę na którą sam nie znam odpowiedzi a męczy mnie to już od dwóch dni i nie mogę dalej ruszyć z pracą. Zagadka dotyczy formatowania warunkowego.

Otóż czy ktoś z osób odwiedzających ten blog wie jak za pomocą VBA znaleźć wszystkie komórki w danym wierszu w których formatowanie warunkowe jest prawdą. Czyli komórka np podświetla się na czerwono, bo nie został spełniony warunek zapisany w formatowaniu warunkowym.
Spróbuje jeszcze jaśniej. mamy 10 komórek w wierszu. Każda posiada formatowanie warunkowe (jeżeli komórka nie równa się 6 to podświetl na czerwono). Wszystkie komórki mają wartość 6 tylko np przedostatnia wartość 9 (i tym samym świeci na czerwono). Jak za pomocą VBA znaleźć adres tej komórki.
Od razu zaznaczam, że polecenie Interior.ColorIndex nie działa. Gdyż zwraca ono „oryginalny kolor komórki” a nie kolor z formatowania warunkowego.
Ewentualne podpowiedzi proszę wysyłać na adres mrjerry@orange.pl Chętnie pomogę też w różnych Waszych problemach z VBA.
Pozdrawiam.

frydo30

Mam pytanko!!!
Zastosowałem formatowanie warunkowe dla komórek w exelu. I tak jeżeli wystepuje tam format liczbowy to owszem działa Jeżeli natomist wprowadzę tekst lub znaki – tam to nie działa.

aagatt

Bardzo się przydało :)

Rambo

Trik bliski mojemu problemowi, natomiast poszukuję odpowiedzi czy da się zrobić, aby aktywną komórkę wrzucić do WYSZUKAJ.PIONOWO? Tzn., zakładając, że aktywna zawsze będzie w pierwszej kolumnie, funkcja ją „wyszukała” i zwróciła wartość z określonej komórki… albo bardziej leniwie – wyszukała wartość z określonej kolumny w jakiejkolwiek aktywnej komórce w wierszu…

Jeśli ktoś zna odpowiedź będę wdzięczny za pomoc:)

Kovalenko

Fajna sprawa, sprawa ale jest problem:

Po wpisaniu kodu VBA, brak możliwości cofania zmian.
Można to jakość obejść

SIEMCE

Mała poprawka do ściągniętego ciekawego przykładu, bo nie bo podświetla się kolumna aktywnej komórki:
W zakresie C9:l30 jest też formatowanie warunkowe dla kolumny:
jest: =KOLUMNA(J18)=AktywnaKolumna – a powinno być: =NR.KOLUMNY(J18)=AktywnaKolumna
wtedy można dopisać wiersz dotyczący podświetlania kolumny:
ActiveWorkbook.Names(„AktywnaKolumna”).RefersTo = „=” & ActiveCell.Column
do procedury:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveWorkbook.Names(„AktywnyWiersz”).RefersTo = „=” & ActiveCell.Row
ActiveWorkbook.Names(„AktywnaKolumna”).RefersTo = „=” & ActiveCell.Column
End Sub
tworząć najpierw podobnie jak do wiersza nazwę dla kolumny AktywnaKolumna …=0
wtedy podświetla wiersz oraz kolumnę aktywnej komórki z zakresu.

janetap

a ja od kilku dni próbuję zrobić jeszcze coś innego ale za nic mi nie wychodzi; liczę na Waszą pomoc. Mam arkusz z danymi powiedzmy 5 kolumn i chcę zrobić coś takiego aby w momencie kiedy w 5 kolumnie w jakiejś komórce zostaną uzupełnione dane (w moim przypadku gdy wpiszę w niej datę) to automatycznie dany wiersz z danymi pokoloruje się powiedzmy na zielono. Proszę o pomoc w tej kwestii.

tomass

dokładnie nie cofa zmian ….. ktoś coś poradzi

ELP

1.Zaznaczamy cały arkusz i wprowadzamy formatowanie warunkowe :
warunek1 „Formuła jest” =LUB(KOMÓRKA(„wiersz”)=WIERSZ())
2.Aby zaznaczanie było odświeżane przy zmianie komórki otwieramy VBA (Alt+F11) i dla arkusza wpisujemy procedurę:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = True
End Sub

To wszystko

Marek

Dzięki SIEMCE, sam kolumnom nie mogłem poradzić, a tego szukałem.

Janek2007

potwierdzam, w arkuszu w którym zastosowałem „podświetlanie” nie mam możliwości cofania zmian. czy ma ktoś rozwiązanie problemu. (podświetlanie robiłem wg wytycznych autora bloga.

rybcoq2

Witam,

Makro działa świetnie, wielkie dzięki dla Autora.

Jednak mam jeden problem. Otóż chciałbym jakoś zmienić kod, żeby podświetlał mi kilka wierszy, bo np. zaznaczylem 5 komórek na raz (obok siebie lub niekoniecznie). Da się to jakos zrobić?

alewoj

Witam,
A czy istnieje takie rozwiązanie dla aktywnej komórki w przypadku całego arkusza?

AKSa

Pytanie do Marcina, odnośnie działania formuły: =NIE(CZY.BŁĄD(SZUKAJ.TEKST(„….”;$A1)))
Formuła działa ŚWIETNIE, ale chciałabym zrozumieć jej zasadę, czy mógłbyś wyjaśnić jak to tego zapisu doszedłeś?

libka

A co trzeba zrobić aby podświetlała się kolumna wskazana np w komórce A1, czyli np. A1=1 to podświetla się kolumna 1, A1=2 podświetla się kolumna 2 itd.

keszyb

Witam,
bazując na artykule bez VBA wpisując formatowanie warunkowe
=(CZY.BŁĄD(SZUKAJ.TEKST(DZIŚ();$A8)))
uzyskałem podświetlenie wiersza z datą dzisiejszą ale tylko w komórkch pustych, komórki z formułami o aktualnym wyniku „0″ nie są podświetlone.
Jak to można zmienić ?
Drugie pytanie dotyczy formatowania warunkowego i wyróżnianie aktywnego wiersza ▬ a jak to będzie wygłądało dla kolumny ?

Pozdrawiam:
keszyb

keszyb

Witam,
niestety u mnie nie działa podświetlanie kolumn, robię wszystko według wskazówek SIEMCE a dostaję tylko monit o błędzie oraz poprawieniu komendy w VBA (Run-time error ’9′)

Pozdrawiam:
keszyb

Marynio

Super przydatne – zaczynam kochać Excela!

Domus

Mnie natomiast ciekawi jak zrobić jedną modyfikację pokazanego przykładu. Chodzi o to by podświetlenie aktywnego wiersza było jeszcze uzależnione od wartości komórki. Czyli jeśli w określonej kolumnie mam wartość „X” to aktywny wiersz podświetla się na czerwono, a jeśli jest wartość „Y” to na zielono.

Leave a Comment

Previous post:

Next post: