Using custom formatting you can test for different conditions. You can use different formatting when different conditions are met. For example if the value in a cell is greater than 5 you can apply a custom formatting to color the font red, and if its less than 5 to color it green.

Comparison Operators:

There are 6 different comparison operators you can use:

Syntax 1:

There are 2 different syntaxes for using conditions in custom formatting. The first type only has 1 condition

Note the semicolon was previously used to determine whether the value in the cell is positive or negative. When using conditions in custom formats the semicolon rule is overridden. For more information about the semicolon character in custom formats please see the link below:

• Excel, Custom Formatting Semicolon Character

Syntax 1, Example 1:

In this example the custom formatting checks if the value in the cell is greater than 10. If the value is greater than 10 the postfix text string “OK” is added to the number. If the number is smaller than 10 the postfix “NG” is added to the number:

[<10]0" OK";0" NG"


The custom formatting above checks if the value of the cell is smaller than 10 or not:

[<10]


If it was smaller than 10, the formatting following the condition will apply:

0" OK"


This formatting prints the number as it is, and add the trailing text string ” OK”. If the value in the cell is not smaller than 10 the formatting following the first semicolon will apply:

0" NG"

Result:

Syntax 1, Example 2:

In this example the condition will check if the value in the cell is greater than 1,000,000 or not. If it is greater than 1,000,000 the value will be displayed as follows:

Value = 1,234,465

Value to display =1.2M

If the value in the cell is smaller than 1,000,000 the value will be displayed as follows

Value = 374,465

Value to display = 374.5k

This can be achieved using the custom formatting below:

[>1000000]0.0,,"M";0.0,"k"


The formatting above uses the conditional statement below to check if the value in the cell is greater than 1,000,000 or not:

[>1000000]


If the value in the cell is greater than 1,000,000 the formatting below will apply. The formatting below divides the number by 1,000,000  displays it using one decimal place and adds an “M” to end of the number:

0.0,,"M"


If the value is smaller than 1,000,000 the formatting below will apply. The formatting below divides the number by 1000, displays it using one decimal place and adds a “k” to the end of the number:

0.0,"k"


Result:

Syntax 2:

You could also use 2 conditions. If the first condition is not met then the second condition will be checked:

Syntax 2, Example 1:

In this example if the value in the cell is greater than 2000, the font color will be changed to red. If its smaller than 100 it will be changed to blue. The custom formatting used can be seen below:

[>2000][Red]0;[<100][Blue]0;0


Condition 1:

[>2000]


Formatting if True:

[Red]0


Condition 2:

[<100]


Formatting if true:

[Blue]0


Formatting if both conditions fail:

0


Result:

You can download the file related to this article from the link below

• Custom Formatting Operators.xlsx

See also

•  Excel, Custom Formatting Semicolon Character
• Excel, Custom Formats
• Excel Custom Formatting, 0, # and ? characters
• Excel, Custom Formatting Colors
• Excel Custom Formatting Adding Characters and Text

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website  www.software-solutions-online.com