Jak zmienić kolor komórek arkusza w zależności od zakresów wartości?

Skrypciarze odpowiadają na Wasze pytania

Jak zmienić kolor komórek arkusza w zależności od zakresów wartości?

Cześć, Skrypciarze! Potrzebny mi skrypt, który jest w stanie odczytać wszystkie wartości w arkuszu, a następnie zmienić jego kolor tła w zależności od wartości. Jeżeli np. wartość komórki jest mniejsza niż 20, to tło komórki przybiera jakiś kolor, dla komórek o wartości 20-40 inny kolor i tak dalej. Czy da się to jakoś zrobić?

-- ESW

Cześć, ESW! Obudziłem się dzisiaj i nie uwierzycie co zobaczyłem: śnieg! Tak! W dobie globalnego ocieplenia śnieg w środku zimy to rzadkość. Leniwie spojrzałem więc za okno (wyglądanie za okno wymaga przecież wysiłku, nikt mi nie wmówi, że jest inaczej) i pomyślałem, że padający śnieg to wspaniała wręcz okazja by jeszcze pospać. Co niezwłocznie uczyniłem, oczywiście.

Niestety, nic nie trwa wiecznie, więc zwlokłem się z łóżka i z ciężkim sercem zaparzyłem kawę (to też wymaga przecież wysiłku). I gdy tak zajadałem pączka, popijając kawą, zobaczyłem za oknem sąsiada, który mozolnie usiłował wydostać się swoim pojazdem z podziemnego garażu. I za każdym razem, gdy był już ot, ciut od celu, koła odmawiały posłuszeństwa, a on znikał ponownie w swoim garażu. Gdy zajadałem trzeciego pączka (to niezdrowe, wiem, wiem), ujrzałem, że sąsiad czerwony ze złości po raz kolejny nie osiągnął celu. Zdecydowałem: jedynym sposobem, aby przetrwać ten dzień - jest... zjadanie pączków na czas, włóczenie się po domu, przyswajanie kofeiny i oglądanie zmagań kierowców z zimą w telewizorze.

No i , hm... o czymś zapomniałem... hm, ach tak!!! Pisanie skryptów!

Tak czy siak, za oknem sypie śnieg z deszczem (blah!!!), pączki się skończyły, kawa wystygła, a w telewizji jedyne co można obejrzeć to 1798 odcinek „Pogody dla Kogośtam”. Chyba więc lepiej opiszę, jak napisać skrypt, który zmienia kolor tła komórek, biorąc pod uwagę zakres ich wartości:

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True



Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Test.xls")

Set objWorksheet = objWorkbook.Worksheets(1)



Set objRange = objWorksheet.UsedRange



For Each objCell in objRange

    If IsNumeric(objCell.Value) Then

       

        If objCell.Value < 20 Then

            objCell.Interior.ColorIndex = 3

        ElseIf objCell.Value < 40 Then

            objCell.Interior.ColorIndex = 6

        ElseIf objCell.Value < 60 Then

            objCell.Interior.ColorIndex = 45

        End If

    End If

Next

Jak widać gołym okiem, dzisiejszy skrypt nie należy do najbardziej skomplikowanych. Zaczynamy od utworzenia wystąpienia obiektu Excel.Application i ustawiamy jego właściwość Visible na True. Daje to nam uruchomione i widoczne na ekranie wystąpienie programu Microsoft Excel. Następnie, poniższe dwa wiersze kodu otwierają plik C:\Scripts\Test.xls i łączą się z pierwszym arkuszem w pliku:

Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Test.xls")

Set objWorksheet = objWorkbook.Worksheets(1)

Następny wiersz kodu tworzy nam wystąpienie obiektu Range programu Excel, czyli zakres, który zawiera w sobie wszystkie komórki zawierające dane:

Set objRange = objWorksheet.UsedRange

Po co nam taka kolekcja komórek zawierających dane? Bingo, bardzo ułatwia to przejście pętlą przez wszystkie komórki, którego to zabiegu będziemy używać po to, by zmienić kolor ich tła, jeżeli będą należały do jakiegoś żądanego zakresu wartości.

Zaczynamy więc od utworzenia pętli For Each, która przejdzie przez wszystkie elementy zakresu (tzn. wszystkie komórki w naszym arkuszu). Wewnątrz pętli używamy funkcji IsNumeric, która sprawdza czy wartość (Value) komórki jest jakąś liczbą, a jeżeli jest, to używa następującego fragmentu kodu, który ustala jej dokładną wartość i sprawdza, czy można ją przyporządkować jakiemuś zakresowi wartości:

If objCell.Value < 20 Then

    objCell.Interior.ColorIndex = 3

ElseIf objCell.Value < 40 Then

    objCell.Interior.ColorIndex = 6

ElseIf objCell.Value < 60 Then

    objCell.Interior.ColorIndex = 45

End If

W pierwszym wierszu sprawdzamy czy wartość komórki jest mniejsza niż 20. Jeżeli jest, to następujący wiersz kodu zmienia Komor tła komórki na czerwony:

objCell.Interior.ColorIndex = 3

Załóżmy teraz, że wartość komórki jest mniejsza niż 20. W takim przypadku kolor tła zmienia się na czerwony i automatycznie wychodzimy z fragmentu If Then – w przypadku gdy VBScript napotka spełnienie jakiegoś warunku, wychodzi z twierdzenia If Then. Co jednak, jeżeli wartość nie jest mniejsza niż 20? W takim wypadku sprawdzamy kolejny warunek: czy wartość jest mniejsza niż 40? Jeżeli jest, to zmieniamy kolor tła na żółty (6). Jeżeli nie to pozostaje nam kolejny warunek, czyli – czy wartość jest mniejsza niż 60? Jeżeli jest, to kolor tła zmienia się na pomarańczowy (45), jeżeli nie, to nic nie zmieniamy, a kolor tła pozostaje taki, jaki był. Wracamy następnie do początku pętli i powtarzamy proces dla kolejnej komórki w arkuszu.

Powinien wyglądać w następujący sposób:

Piękna i funkcjonalna, zupełnie tak, jak Skrypciarze…

Teraz trochę trudniejsza kwestia – co ze sobą zrobić? Zjeść kolejnego pączka czy coś konkretniejszego, np. hamburgera? Wypić kolejną kawę czy herbatę (a może Colę)? Pójść spać czy nie? Rety, praca z domu jest naprawdę skomplikowana i wymaga równie skomplikowanego procesu decyzyjnego…

(…)

Jednak pączek.

Do początku strony