Od 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.
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
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.
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.
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.
{ 31 comments… read them below or add one }
W załączniku zamiast pliku xls jest dokument z artykułem.
Uwaga bardzo trafna
Dziękuję. Plik już podmieniony.
Marcin
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
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
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
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
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
@margerytka
Sugeruję poczytać o ukrywaniu kolumn i wierszy
Marcin
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 
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.
@ 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
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.
Fajny trik.
Jedna uwaga – przeliczanie arkusza musi być ustawione na Automatyczne
Pozdrawiam
przyda sie, fajna stronka pozdr
A co zrobić żeby również była wyróżniona (podświetlona) aktywna kolumna?
Pozdrawiam
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
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?
Bardzo przydatna funkcja ta “=NIE(CZY.BŁĄD(SZUKAJ.TEKST(”*grz*”;$C1)))”
Już mi śmiga w raporcie
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.
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.
Bardzo się przydało
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:)
Fajna sprawa, sprawa ale jest problem:
Po wpisaniu kodu VBA, brak możliwości cofania zmian.
Można to jakość obejść
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.
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.
dokładnie nie cofa zmian ….. ktoś coś poradzi
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
Dzięki SIEMCE, sam kolumnom nie mogłem poradzić, a tego szukałem.
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.
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ć?
Witam,
A czy istnieje takie rozwiązanie dla aktywnej komórki w przypadku całego arkusza?