Meni često desi da imam potrebu obojiti neku ćeliju u Excel dokumentu na osnovu vrijednosti upisane u tu ćeliju. Ako se radi samo o vrijednosti u toj ćeliji, onda je to moguće postići pomoću opcije Conditional Formatting. Pomoću ove opcije je moguće podesiti da se boja ćelije mijenja na osnovu upisane vrijednosti ali po unaprijed podešenom kriteriju (vrijednosti).
Međutim, meni je češće potrebno da se boja ćelije mijenja na osnovu vrijednosti koja nije poznata u trenutku kreiranja kriterija, odnosno, da se boja ćelije u nekoj koloni mijenja na osnovu odnosa vrijednosti u toj ćeliji i vrijednosti u odgovarajućoj ćeliji neke druge kolone, kao na slici iznad.
Za to je potrebno koristiti VBScript odnosno VBA (Visaul Basic for Applications). Kao primjer sam kreirao Excel dokument sa tabelom koja sadrži 11 vrsta i 3 kolone, gdje je prva vrsta zaglavlje tabele.
Prva kolona sadrži redni broj podataka, druga kolona planirani broj komada a treća kolona urađeni broj komada. Upisom vrijednosti u ćeliju kolone Urađeno mijenja se boja te ćelije odvisno od toga da li je upisana vrijednost manja, jednaka ili veća od vrijednsoti u ćeliji lijevo, odnosno u koloni Potrebno. Isto tako, ako je u kolonu Urađeno već upisana vrijednost, promjenom vrijednosti u koloni Potrebno mijenja se boja odgovarajuće ćelije u koloni Urađeno.
Ispod možete preuzeti Excel dokument Oboji_kolonu_na_osnovu_vrijedosti_u_drugoj_koloni.xlsm koji sadrži opisani primjer:
Excel dokument je sa extenzijom .xlsm (Macro-enabled Excel workbook) zato što sadrži VBA kôd.
Napomena: Uvijek budite pažljivi pri pokretanju .xlsm dokumenta ako dolaze sa nepoznatog izvora jer ugrađeni VBA kôd može biti virus ili, u najboljem slučaju, neki neželjeni VBA kôd.
Pri otvaranju dokumenata ovog tipa, odvisno od sigurnosnih postavki u Excel-u, može se pojaviti poruka da su makroi onemogućeni (security warning macros have been disabled). Potrebno je kliknuti na dugme Options i u dobijenom prozoru markirati opciju Enabel this content da bi bilo omogućeno izvršavanje VBA kôd.
Postoji mogućnost da je u Excel-u potpuno zabranjeno otvaranje dokumenta koji sadrže macro naredbe. Da bi bojenje ćelija radilo, zabranu je potrebno isključiti (barem za ovaj dokument - dodati ga u exception listu).
Kako radi ovo bojenje ćelije na osnovu poređenja vrijednosti u dvije ćelije?
Prvo je potrebno deklarisati varijable koj eće biti korištene:
Dim boja1 As Integer, boja2 As Integer, bijela As Integer, i As Integer
Nakon toga, u proceduri Private Sub Worksheet_SelectionChange(ByVal Target As Range) definisati boje koje će biti korištene:
'Setovanje bojaboja1 = 3boja2 = 6bijela = 2
Brojevi predstavljaju boje i to: 3 - crvena, 6 - žuta i 1 - bijela.
Listu boja možete pronaći u samom Excel duokumentu, desno od tabele sa primjerom.
Dalje, u istoj proceduri kreirati For petlju koja broji od prve vrste tabele koja sadrži podatke (označeno lijevo od tabele - bez zaglavlja), do zadnje vrste koja sadrži podatke.
'Brojač i ide od prve do zadnje vrste u kojima su ćelije čija vrijednost se poredi (u ovom primjeru od 3. do 12.)For i = 3 To 12
Unutar ove For petlje se nalazi If naredba koja prvo provjerava da li je ćelija kolone Urađeno, koja će biti obojena, prazna. Ako jeste, oboji je bijelom bojom.
Ako ćelija kolone Urađeno nije prazna, druga If naredba provjerava da li je vrijednost u toj ćeliji manja, jednaka ili veća od vrijednosti u odgovarajućoj ćeliji kolone Potrebno i na osnovu rezultata poređenja oboji ćeliju.
'Provjera da li je desna ćelija prazna
If Cells(i, 4).Value = "" Then
'Ako je prazna - pozadina je bijela
Cells(i, 4).Interior.ColorIndex = bijela
Else
'Ako desna ćelija nije prazna
If Cells(i, 3).Value > Cells(i, 4).Value Then
'Ako je vrijednost u lijevoj ćeliji VEĆA od vrijednosti u desnoj ćeliji - oboji desnu ćeliju u boja2
Cells(i, 4).Interior.ColorIndex = boja2
ElseIf Cells(i, 3).Value < Cells(i, 4).Value Then
'Ako je vrijednost u lijevoj ćeliji MANJA od vrjednosti u desnoj ćeliji - oboji desnu ćeliju u boja1
Cells(i, 4).Interior.ColorIndex = boja1
Else
'Ako je vrijednost u lijevoj ćeliji JEDNAKA vrjednosti u desnoj ćeliji - oboji desnu ćeliju u bijela
Cells(i, 4).Interior.ColorIndex = bijela
End If
End If
Ovaj kod se izvršava prilikom svakog prelaska iz ćelije u ćeliju Excel dokumenta. To znači da, nakon klika u neku ćeliju Excel dokumenta, petlja provjeri sve vrste u rasponu od 3 do 12 (u ovom slučaju i ide od 3 do 12) i kolone 3 i 4 (u ovom primjeru). To znači da će pri svakom kliku u neku ćeliju biti provjereno 18 ćelija. Ako radimo sa ogromnom tabelom, od više hiljada vrsta, moglo bi doći do usporavanja rada. Međutim, za normalne tabele to nije problem.
Na osnovu rezultata poređenja vrijednosti u svakoj vrsti kolone 3 i kolone 4 biće obojena ćelija kolone 4.
Gornji primjer je moguće prilagoditi po želji za bilo koju tabelu. Potrebno je prvo odrediti koje dvije kolone se porede (u ovom primjeru kolona 3 i kolona 4) i korisiti njihove redne brojeve (umjesto 3 i 4). Ostaje još da se podesi raspon vrsta koje se provjeravaju. To se podešava u For petlji tako što se izmijeni početna i krajnja vrijednost brojača i (For i = 3 To 12).