Can You Do An If Statement In Excel Based On Cell Formatting Such As Color? (It Is Possible Without VBA)

By Paul Smart •  Updated: 12/01/22 •  10 min read

One of the most common questions we get asked is is it possible to produce an if statement based on cell formatting such as the background color of a cell or whether it has borders, a particular type format or even the cell alignment.

It is possible to use an if statement to identify cells based upon formatting which can include a number of the aspects of the cell which include its color, the color of the text, the alignment of the text and many other characteristics. This can also be done without needing to be able to program in VBA.

However, to achieve this it requires you to create a get cells function within Excel with the correct characteristics to ensure that you can differentiate between cells with different formatting which is not terribly difficult.

How To Create A Get Cell Function

To create a get cells function is relatively straightforward and as mentioned above it can be modified to cover a range of cell formats but for the purposes of this article we will look at how to create a get cell function for color.

Step 1 – Go To The Formulas Tab and then select Name Manager 

Step 2 – Click New To Create the Get Cell Function 

Step 3 – Name the function you are creating. Note that it is best to give the function a name that makes sense and you can find later if necessary.  Enter =Get.cell(38,Sheet1!A1) in the Refers to: area.

Notes: In the get cell command the first argument defines the type of cell formatting that you are looking for in the Formula. In this case 38 is the number that is required for the background color of a cell. There is a list of all the different arguments at the end of the article which will allow you to find exactly the formatting you need to identify in your formula.

Secondly. the default of Excel when you select a cell on your page is to reference the cell with dollar signs which is shown in the screenshot above. To make the cell reference copyable you need to remove the dollar signs from the formula.

Step 4 – Once you click ok on the previous screen you will get the name manager screen come up which you just need to click close to complete the creation of the get cell function.

How To Apply The Get Cell Function In An If Statement 

Having created the get cell function for your desired formatting, in this case color, you can then use it in an if statement to create the function that will be able to pick out the cells with the correct format.

To do this enter the equals sign and select from your list of functions the name of the getcell function of created which in my example is cellcolor. In the screenshot below you will see that once you have created the function it will appear in the list of possible functions you can use with an Excel. Enter =cellcolour to enter it into the cell

When you apply the cell formula to the different cells with different colors you will see that it produces a different number for each color in the cell including the number 0 in cases where there is no color formatting. This can then be used within an if statement to identify the color of the cell and therefore apply a logic statement around it.

However, it’s important to note that if you do not get the expected result then double check the cell references that you are using to ensure that you are in the correct columns. For example when I was setting up this example I used cell A1 as the reference. However, when I actually applied the cell color to the cells in B1 the cell reference became B1 and therefore did not return the expected result. 

Finally, you will need to create a nested if statement depending upon the specific colors in the example that we want to identify. In this example I will create an if statement that returns true if any of the cells contain a colors which is as follows;

=if(cellcolor=0, “cell has no color”, “cell has color”)

Creating An If Statement For Other Types Of Formatting

Exactly the same method can be used to create an if statement for any type of formatting provided that you know the argument required for each one of the different formatting styles covered. A list of the relevant numbers for the cell argument are provided below for your reference. 

Get Cell ArgumentDescriptionComment
1Absolute reference of the upper-left cell in reference, as text in the current workspace reference style.
2Row number of the top cell in reference.
3Column number of the leftmost cell in reference.
4Same as TYPE(reference).
5Contents of reference.
6Formula in reference, as text, in either A1 or R1C1 style depending on the workspace setting.
7Number format of the cell, as text (for example, “m/d/yy” or “General”).
8The cell’s horizontal alignment. Function returns 1 to 7 depending on the alignment.1 = General​, 2 = Left​, 3 = Center​, 4 = Right​, 5 = Fill, ​6 = Justify​, 7 = Center across cells​
9The left-border style assigned to the cell: Function returns 1 to 7 depending on the alignment.0 = No border​, 1 = Thin line​, 2 = Medium line​,3 = Dashed line​, 4 = Dotted line​, 5 = Thick line​, 6 = Double line​, 7 = Hairline​
10The right-border style assigned to the cell. Function returns 1 to 7 depending on the alignment.0 = No border​, 1 = Thin line​, 2 = Medium line​,3 = Dashed line​, 4 = Dotted line​, 5 = Thick line​, 6 = Double line​, 7 = Hairline​
11The top-border style assigned to the cell. Function returns 1 to 7 depending on the alignment.0 = No border​, 1 = Thin line​, 2 = Medium line​,3 = Dashed line​, 4 = Dotted line​, 5 = Thick line​, 6 = Double line​, 7 = Hairline​
12The bottom-border style assigned to the cell. Function returns 1 to 7 depending on the alignment.0 = No border​, 1 = Thin line​, 2 = Medium line​,3 = Dashed line​, 4 = Dotted line​, 5 = Thick line​, 6 = Double line​, 7 = Hairline​
13Cell pattern for selected cell.​Returns a number from 0 to indicating the pattern of the selected cell​. If no pattern is selected, returns 0.​
14The cell is lockedIf locked TRUE; otherwise, returns FALSE.
15The cell’s formula is hiddenIf hidden TRUE; otherwise, returns FALSE.
16A 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).​
17Row height of cell, in points.
18Name of font, as text.
19Size of font, in points.
20The text is boldIf all the characters in the cell, or only the first character, are bold, returns TRUE; otherwise, returns FALSE.
21The text is ItalicIf all the characters in the cell, or only the first character, are italic, returns TRUE; otherwise, returns FALSE.
22The text is UnderlinedIf all the characters in the cell, or only the first character, are underlined returns TRUE; otherwise, returns FALSE.
23The text is struck throughIf all the characters in the cell, or only the first character, are struck through, returns TRUE; otherwise, returns FALSE.
24Font ColorFont color of the first character in the cell, as a number in the range 1 to 56. If font color is automatic, returns 0.
25The characters in the cell are outlinedIf 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.​
26Shadow FontIf 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.​
27Page Break at the cell0 = No break​, 1 = Row​, 2 = Column​, 3 = Both row and column​
28Row level (outline).
29Column level (outline).
30If the row containing the active cell is a summary row, returns TRUE; otherwise, returns FALSE.
31If the column containing the active cell is a summary column, returns TRUE; otherwise, returns FALSE.
32Returns the name of the workbook and sheetName of the workbook and sheet containing the cell If the window contains only a single sheet that has the samename 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”.​
33Formatted To Wrap text in cellReturns true of false
34Left-border colorReturns color as a number in the range 1 to 56. If color is automatic, returns 0.
35Right-border colorReturns color as a number in the range 1 to 56. If color is automatic, returns 0.
36Top-border colorReturns color as a number in the range 1 to 56. If color is automatic, returns 0.
37Bottom-border colorReturns color as a number in the range 1 to 56. If color is automatic, returns 0.
38Shade foreground colorReturns color as a number in the range 1 to 56. If color is automatic, returns 0.
39Shade background colorReturns color as a number in the range 1 to 56. If color is automatic, returns 0.
40Style of the cell, as text.
41Returns the formula in the active cell without translating it (useful for international macro sheets).
42The 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.​
43The 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.​
44The 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.​
45The 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.​
46Cell contains a text noteA text note, returns TRUE; otherwise, returns FALSE.
47Cell contains a text noteA sound note, returns TRUE; otherwise, returns FALSE.
48Cells contains a formulaA formula, returns TRUE; if a constant, returns FALSE.
49If the cell is part of an array, returns TRUE; otherwise, returns FALSE.
50The cell’s vertical alignment:1 = Top​, 2 = Center​, 3 = Bottom​, 4 = Justified​
51The cell’s vertical orientation.0 = Horizontal​, 1 = Vertical,​ 2 = Upward​, 3 = Downward​
52The cell prefix (or text alignment) character, or empty text (“”) if the cell does not contain one.
53Contents of the cell as it is currently displayed, as text, including any additional numbers or symbols resulting from the cell’s formatting.
54Returns the name of the PivotTable view containing the active cell.
55Returns the position of a cell within the PivotTableView.
56Returns the name of the field containing the active cell reference if inside a PivotTable view.
57SuperscriptsReturns TRUE if all the characters in the cell, or only the first character, are formatted with a superscript font; otherwise, returns FALSE.
58Font StyleReturns 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”.
59Underline Style1 = none​, 2 = single​, 3 = double​, 4 = single accounting​, 5 = double accounting
60SubscriptReturns TRUE if all the characters in the cell, or only the first character, are formatted with a subscript font; otherwise, it returns FALSE.
61Returns the name of the PivotTable item for the active cell, as text.
62Name of workbook and sheetReturns the name of the workbook and the current sheet in the form “[book1]sheet1”.
63Returns the fill (background) color of the cell.
64Returns the pattern (foreground) color of the cell.
65Returns TRUE if the Add Indent alignment option is on (Far East versions of Microsoft Excel only); otherwise, it returns FALSE.
66Name of workbookReturns the book name of the workbook containing the cell in the form BOOK1.XLS.

Relevant Articles

How Do You Copy A VLOOKUP Formula Without Changing The Table Array?

Why Is The Vlookup Returning #N/A When Value Exists? What Is The Problem And How To Fix It?

What Is Opposite Of Concatenate? How Do I Unconcatenate Text?

What Does #### Mean In Excel? And How To Fix The Problem

Paul Smart