# Conditional formatting depending on the formula that the cell contains

I have a spreadsheet that has 3 rows. Total received, Share A, and Share B. Share A will have a formula that is either `=A1*0.8` or `=A1*0.9`. Is there any way for me to set up a conditional format that will make the cell a different color depending on which mathematical formula I use?

I'm trying to be able to easily tell whether Share A is getting 80% or 90%.

• The value of total received will be different every time so I cannot use a number matching format – John C Mar 4 '18 at 3:55

I don't think there is any built-in function that can access the formula in a cell.

If you want to go that route, you can use Apps Script function `getFormula()`.

My recommendation is that you adopt the following convention for yourself.

As much as possible,

always organize your data in two dimensions: each row is a new instance of your data type, each column is an attribute of your data type.

If you do that, you can easily apply Conditional Formatting, among many other things, based on column index. You can also apply Conditional Formatting based on the strings in the first row, ie. your header.

In your example, there should be 3 columns. Each column contains

• ID/name of share;

• share type (or a formula to output share based on the ID/name of your share);

• and total received, which is a formula that computes your "total received" based on share type

respectively.

You can use `arrayformula` to apply your formula automatically and centralize your formulas in one cell. For example, in `C2`, enter

``````=arrayformula(if(isblank(A2:A100,,ifs(exact(B2:B100,"Share Type 1"),A2:A100*0.8,
exact(B2:B100,"Share Type 2"),A2:A100*0.9))
``````

Use `B2:B100` or some relatively small number instead of `B2:B` to reduce run time. The `isblank()` check will ensure that the formula is only applied to the rows you entered share information on.

The custom formula for Conditional Formatting would be

``````=if(isblank(c2),false,exact(B2,"Share Type 1"))
``````

with range `C2:C` for one color and duplicate the same thing for "Share Type 2" for another color.

There are a few things you can do in order to conveniently enter/output the type of the share. For example,

• you can use Data Validation and then enter the type manually. Data Validation will help you enter the correct string every time and it also comes with a simple complete-what-you-type function;

• or, if the type of a given share is fixed over time, you can have a list of all shares on a separate tab/sheet and each type of shares under a different column. Also enter the associated coefficients there using `arrayformula`. Then use `vlookup` on your main tab/sheet to retrieve those coefficients conveniently.

Custom Formula:

``````=AND(B1=\$A1*0.9,ISNUMBER(B1))
``````

Apply to:

``````B:C
``````