Excel Forum - Porady, Pomoc, Excel Help, Excel FAQ - 58. Odczyt koloru tła komórek z formatem warunkowym

Maciej Gonet · Excel Expert Wersja: Win Office 365 Posty: 10323

Od dawna znany jest problem z programowym odczytem efektywnego formatowania komórek sformatowanych warunkowo. Właściwości Range.Interior.Color i Range.Font.Color uwzględniają tylko stałe formatowanie komórek, a pomijają warunkowe. Dostęp do wyników formatowania jest bardzo utrudniony, gdyż trzeba po kolei analizować wszystkie zdefiniowane reguły formatowania, a ponadto sprawdzać, które reguły są spełnione, a w razie spełnienia kilku, która wywołuje w końcu efekt w arkuszu. Nadzieję na poprawę tej sytuacji rozbudziło wprowadzenie w Excelu 2010 obiektu DisplayFormat, który w założeniu miał udostępnić użytkownikowi końcowy efekt nałożenia formatów stałych i warunkowych zastosowanych w komórce. Niestety, Microsoft w opisie podał informację, że obiektu tego nie można wykorzystać w funkcjach UDF, wywoływanych z arkusza, a takie zastosowanie byłoby najbardziej pożądane. Można go wykorzystać w makroinstrukcjach lub funkcjach wywoływanych wewnątrz kodu. Można na przykład napisać makro, które wyświetli informację w MsgBox. Można również wykorzystywać go w procedurach zdarzeniowych.
Zastanawiałem się jak można obejść to ograniczenie. Przejrzałem dostępne zasoby Internetu, ale nie znalazłem tam żadnych konstruktywnych sugestii. Wymyśliłem rozwiązanie, które wydaje się być dość mało znane, więc chcę się podzielić nim z Czytelnikami.
Pomysł polega na utworzeniu w pamięci tablicy publicznej, która okresowo, za pomocą procedury zdarzeniowej będzie zapełniana kodami kolorów (lub innego potrzebnego atrybutu) pobieranych za pośrednictwem obiektu DisplayFormat. W arkuszu podajemy adres zakresu nadzorowanego i nadajemy nazwę temu zakresowi (w przykładzie nazwa spvis). Tekst adresu w komórce B2, definicja nazwy:

Kod:

spvis : = ADR.POŚR(Arkusz1!$B$2)

Przygotowujemy również komórki z wzorcami barw (dla uproszczenia sformatowane ręcznie).
W module standardowym umieszczamy kod:

Kod:

Public MirTab() As Long

Sub Mirror(rng As Range)
ReDim MirTab(1 To rng.Count)
Dim i As Long
For i = 1 To rng.Count
MirTab(i) = rng.Cells(i).DisplayFormat.Interior.Color
Next i
End Sub

Function Licz_Kolory(pattern As Range) As Long
Application.Volatile
Dim i As Long
For i = 1 To UBound(MirTab)
If MirTab(i) = pattern.Interior.Color Then _
Licz_Kolory = Licz_Kolory + 1
Next i
End Function

W module arkusza umieszczamy procedurę obsługi zdarzenia Change lub SelectionChange.

Kod:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call Mirror(Range("spvis"))
ActiveSheet.Calculate 'opcjonalnie
End Sub

Funkcja Licz_Kolory zlicza komórki o określonym kolorze tła wewnątrz zakresu nadzorowanego, zgodnym z kolorem wzorca, przy czym nie rozróżnia komórek sformatowanych na stałe od sformatowanych warunkowo. Funkcję można wywołać z arkusza lub z kodu. Funkcja nie reaguje natychmiast na zmiany koloru komórek, najpierw musi zostać wygenerowane zdarzenie, które spowoduje odświeżenie tablicy kolorów, a następnie zdarzenie wywołujące przeliczenie funkcji. Ponieważ funkcja jest zdefiniowana jako ulotna, więc będzie przeliczana po edycji dowolnej komórki lub po naciśnięciu klawisza F9. Można tę reakcję przyspieszyć dopisując polecenie przeliczenia arkusza po każdej zmianie wyboru komórki. Ale przy dużych arkuszach to może spowolnić ich działanie. Wybór zależy od arkusza, w którym to chcemy zastosować.
W przykładzie komórki zawierające liczby są sformatowane warunkowo, natomiast komórki bez liczb są sformatowane na stałe.

Kolory_test_2010.xlsm

Pobierz Plik ściągnięto 723 raz(y) 17.25 KB