# Tag Info

0

Both data validation and conditional formatting can reference the values in other cells. For example, if I require all values in column A to be greater than the value in cell C1, I would use Data Validation with custom formula =A1>C\$1. The logic is as follows: Validation rules (and conditional formatting rules) are entered as they should apply to the ...

1

The ISBLANK formula should serve your purpose well. Create a new rule in the conditional formatting menu. Set the range of your rule to D2:D so that the formatting is applied to column D. Then, in the Condition dropdown menu, select "Custom formula is." In the input field, enter NOT(ISBLANK(C2:C)). (The NOT formula negates the value returned by its ...

1

You cannot use the formatting of a cell as input to a formula. But, it is easy to extend the range of conditional formatting so that it applies to A as well as to B, while being based on the data from B. For example, formatting the range A2:B according to the custom formula =LEN(\$B2) colors both A and B if the column B cell is nonempty. More ...

1

Apply to a range such as A2:Z the conditional formatting with the custom formula =and(\$L2="APPROVED", \$M2="APPROVED", \$N2="APPROVED", \$O2 = "X") Explanation The formula is written as it should be applied to the upper left corner of the range, that is A2 here. Dollar signs are used to make column references absolute, so that, for example, formatting of ...

1

Apply to range A1:B conditional formatting based on custom formula =\$A1=MAX(\$A\$1:\$A) The formula =X=Y returns 1 (true) is X=Y, and 0 (False) otherwise. So, formatting is applied (to both A and B) if the value in A column is equal to the maximal value in A column, as you wanted. The dollar signs (absolute references) are needed so that as the formatting ...

1

You can do this by applying to range A1:A the conditional formatting with custom formula =counta(unique(transpose(split(A1&", "&JOIN(", ",B\$1:B),", "))))>counta(unique(B\$1:B)) Explanation. To the right of the inequality sign, counta(unique(B\$1:B)) counts the number of distinct words in column B. On the left, A1&", "&JOIN(", ",B\$1:B) ...

3

Use conditional formatting in cell J3 with the formula =AND(ISBLANK(F3)=FALSE,G3>0) Step-by-step: Select cell J3 and click on Format > Conditional formatting Select the options as in the screenshot below and paste the above formula in the required field. Conditional formatting is applied if the formula results to TRUE. This will only be the case if ...

3

You don't need a script for that; the built-in conditional formatting tools are flexible enough. Assuming the first row is for headers, apply conditional formatting to the range A2:A, with custom formula =len(join("", B2:Z2)). This means: join the content of cells, and find its length; the formatting applies if the length is greater than 0. Replace Z by AZ ...

Top 50 recent answers are included