Conditional formatting in Google Sheets: Can I use custom function in the 'custom formula is:' field?

Short answer

A custom function can't be used as a part of a custom formula in the Google Sheets' conditional formatting built-in user interface.

Explanation

In Google Sheets, a custom function is like spreadsheet built-in functions but written by the user using the Google Apps Script editor. They can't modify other objects, only return one or multiple values.

By the other hand, a custom function only is calculated when it's added to a cell or when at least one of it's parameters changes. They can't use non-deterministic functions as parameters like NOW(), TODAY(), RAND() and RANDBETWEEN().

Test

In order to test the statement in the short answer, I created a did the following:

1. Create a simple custom function

function two() {
  return 2;
}

2. Add the custom function as part of a custom formula in the conditional formatting side panel

=two()=2

Result: Nothing changed.

References

• Custom Functions in Google Sheets

I have also found that custom functions cannot be used for conditional formatting. However, I located a pretty simple workaround.

The custom formula that I tried to use is: =hasGreen(CELL("address",$H3)&":"&CELL("address",$M3))

ie. format a cell based on a range of other cells in that row.

My solution was to place the above formula into column P. Then I changed my conditional formatting's custom formula to =P3

Worked like a charm. Any change to H3:M3 would call hasGreen and update the value of P3. The conditional formatting would note any change to P3, and adjust the formatting.