Macro 4: Ambil Kode Warna dengan Get.Cell

Updated on November 20, 2024 . 8 min read

Salah satu fungsi yang sering digunakan untuk Macro 4 ini adalah Get.Cell. Fungsi ini banyak digunakan untuk mengambil kode warna yang digunakan di sheet Excel.

Karena seperti yang kita tahu sampai saat ini tidak ada rumus bawaan Excel yang bisa membaca Warna. kecuali Macro 4 atau VBA.

Untuk pembahasa dengan VBA sudah saya bahas di : UDF COUNT Berdasarkan Warna – vba.co.id

Banyak kasus yang mencari cara untuk menghitung cells berdasarkan kriteria warna, untuk menyelsaikan kasus tersebut, salah satunya bisa menggunakan fungsi Macro4 yaitu Get.cell

GET.CELL Macro
Mengembalikan informasi tentang pemformatan, lokasi, atau isi sebuah sel. Gunakan GET.CELL dalam sebuah makro yang perilakunya ditentukan oleh status dari sel tertentu.

Syntax

GET.CELL(type_num, reference)

Ada banyak sekali Type_Num yang bisa digunakan diantaranya

Type_num	Returns
1	Absolute reference of the upper-left cell in reference, as text in the current workspace reference style.
2	Row number of the top cell in reference.
3	Column number of the leftmost cell in reference.
4	Same as TYPE(reference).
5	Contents of reference.
6	Formula in reference, as text, in either A1 or R1C1 style depending on the workspace setting.
7	Number format of the cell, as text (for example, “m/d/yy” or “General”).
8	Number indicating the cell’s horizontal alignment:1 = General2 = Left3 = Center4 = Right5 = Fill6 = Justify7 = Center across cells
9	Number indicating the left-border style assigned to the cell:0 = No border1 = Thin line2 = Medium line3 = Dashed line4 = Dotted line5 = Thick line6 = Double line7 = Hairline
10	Number indicating the right-border style assigned to the cell. See type_num 9 for descriptions of the numbers returned.
11	Number indicating the top-border style assigned to the cell. See type_num 9 for descriptions of the numbers returned.
12	Number indicating the bottom-border style assigned to the cell. See type_num 9 for descriptions of the numbers returned.
13	Number from 0 to 18, indicating the pattern of the selected cell as displayed in the Patterns tab of the Format Cells dialog box, which appears when you click the Cells command on the Format menu. If no pattern is selected, returns 0.
14	If the cell is locked, returns TRUE; otherwise, returns FALSE.
15	If the cell’s formula is hidden, returns TRUE; otherwise, returns FALSE.
16	A two-item horizontal array containing the width of the active cell and a logical value indicating whether the cell’s width is set to change as the standard width changes (TRUE) or is a custom width (FALSE).
17	Row height of cell, in points.
18	Name of font, as text.
19	Size of font, in points.
20	If all the characters in the cell, or only the first character, are bold, returns TRUE; otherwise, returns FALSE.
21	If all the characters in the cell, or only the first character, are italic, returns TRUE; otherwise, returns FALSE.
22	If all the characters in the cell, or only the first character, are underlined, returns TRUE; otherwise, returns FALSE.
23	If all the characters in the cell, or only the first character, are struck through, returns TRUE; otherwise, returns FALSE.
24	Font color of the first character in the cell, as a number in the range 1 to 56. If font color is automatic, returns 0.
25	If all the characters in the cell, or only the first character, are outlined, returns TRUE; otherwise, returns FALSE. Outline font format is not supported by Microsoft Excel for Windows.
26	If all the characters in the cell, or only the first character, are shadowed, returns TRUE; otherwise, returns FALSE. Shadow font format is not supported by Microsoft Excel for Windows.
27	Number indicating whether a manual page break occurs at the cell:0 = No break1 = Row2 = Column3 = Both row and column
28	Row level (outline)
29	Column level (outline).
30	If the row containing the active cell is a summary row, returns TRUE; otherwise, returns FALSE.
31	If the column containing the active cell is a summary column, returns TRUE; otherwise, returns FALSE.
32	Name of the workbook and sheet containing the cell If the window contains only a single sheet that has the same name as the workbook without its extension, returns only the name of the book, in the form BOOK1.XLS. Otherwise, returns the name of the sheet in the form “[Book1]Sheet1”.
33	If the cell is formatted to wrap, returns TRUE; otherwise, returns FALSE.
34	Left-border color as a number in the range 1 to 56. If color is automatic, returns 0.
35	Right-border color as a number in the range 1 to 56. If color is automatic, returns 0.
36	Top-border color as a number in the range 1 to 56. If color is automatic, returns 0.
37	Bottom-border color as a number in the range 1 to 56. If color is automatic, returns 0.
38	Shade foreground color as a number in the range 1 to 56. If color is automatic, returns 0.
39	Shade background color as a number in the range 1 to 56. If color is automatic, returns 0.
40	Style of the cell, as text.
41	Returns the formula in the active cell without translating it (useful for international macro sheets).
42	The horizontal distance, measured in points, from the left edge of the active window to the left edge of the cell. May be a negative number if the window is scrolled beyond the cell.
43	The vertical distance, measured in points, from the top edge of the active window to the top edge of the cell. May be a negative number if the window is scrolled beyond the cell.
44	The horizontal distance, measured in points, from the left edge of the active window to the right edge of the cell. May be a negative number if the window is scrolled beyond the cell.
45	The vertical distance, measured in points, from the top edge of the active window to the bottom edge of the cell. May be a negative number if the window is scrolled beyond the cell.
46	If the cell contains a text note, returns TRUE; otherwise, returns FALSE.
47	If the cell contains a sound note, returns TRUE; otherwise, returns FALSE.
48	If the cells contains a formula, returns TRUE; if a constant, returns FALSE.
49	If the cell is part of an array, returns TRUE; otherwise, returns FALSE.
50	Number indicating the cell’s vertical alignment:1 = Top2 = Center3 = Bottom4 = Justified
51	Number indicating the cell’s vertical orientation:0 = Horizontal1 = Vertical2 = Upward3 = Downward
52	The cell prefix (or text alignment) character, or empty text (“”) if the cell does not contain one.
53	Contents of the cell as it is currently displayed, as text, including any additional numbers or symbols resulting from the cell’s formatting.
54	Returns the name of the PivotTable report containing the active cell.
55	Returns the position of a cell within the PivotTable report.0 = Row header1 = Column header2 = Page header3 = Data header4 = Row item5 = Column item6 = Page item7 = Data item8 = Table body
56	Returns the name of the field containing the active cell reference if inside a PivotTable report.
57	Returns TRUE if all the characters in the cell, or only the first character, are formatted with a superscript font; otherwise, returns FALSE.
58	Returns the font style as text of all the characters in the cell, or only the first character as displayed in the Font tab of the Format Cells dialog box: for example, “Bold Italic”.
7	Returns the number for the underline style:1 = None2 = Single3 = Double4 = Single accounting5 = Double accounting
60	Returns TRUE if all the characters in the cell, or only the first character, are formatted with a subscript font; otherwise, it returns FALSE.
61	Returns the name of the PivotTable item for the active cell, as text.
62	Returns the name of the workbook and the current sheet in the form “[Book1]Sheet1”.
63	Returns the fill (background) color of the cell.
64	Returns the pattern (foreground) color of the cell.
65	Returns TRUE if the Add Indent alignment option is on (Far East versions of Microsoft Excel only); otherwise, it returns FALSE.
66	Returns the book name of the workbook containing the cell in the form BOOK1.XLS.

yang sering digunakan adalah kode 24 dan kode 63, dimana 24 adalah kode yang digunakan untuk mengambil warna Font, sedangkan 63 adalah kode yang digunakan untuk mengambil wana Cell Background.

Contoh Kasus 1

contoh kasus menghitung warna berdasarkan warna background cells

Misalkan kita mau menghitung ada berapa Nama yang diwarnai Pink, Kita bisa menggunakan GetCell untuk Menghitungnya.

Pada kolom F3, buat sebuah Name Range, dan beri nama Warna kemudian isi dengan rumus =GET.CELL(63,Sheet1!C3)

Selanjutnya pada F3 tinggal ketikan =warna

Selanjutnya untuk menghitung berapa jumlah anak yang diwarnai Pink, bisa menggunakan COUNTIF pada kolom F dimana jika pada kolom F berisi 18 maka itu yang diwarnai Pink.

=COUNTIF(F3:F22,18)