conditional formattinggoogle sheets
I have a comment form and sheet on my site:
• Column A: Timestamp
• Column B: Name
• Column C: Comment
I'd like to highlight every row that includes the admin's answer. In other words, if a cell in column B has the text Admin, its entire row should have a background color. How should I use conditional formatting or something like that to achieve it?
You can use a range as a parameter for the ISFORMULA() function.
e.g. If you want to highlight all cells containing formulas in the range A1:J10, then you can use the formula =ISFORMULA(A1:J10) and apply it to the range A1:J10.
Note that this works with normal ranges (e.g. B2:F30), but not with infinite ranges (e.g. B:F).
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 propagates throughout both columns, the references are still made to column A.
If there is a header row, adjust accordingly, applying
=$A2=MAX($A$2:$A)
to the range A2:B.
You can make this work on a single column or more than two columns: just change the range of formatting. The formatting formula should be written as it is to be applied to the upper left corner of the range.
Best Answer
Apply conditional formatting to the range A2:C with custom formula
The idea is that the formula should be written as it applies to the upper left corner of the formatted range, in this case A2. It will be automatically adjusted for other cells: for example, cell C6 will refer to B6, because row reference is relative while column reference is absolute.
Using
regexmatchwith the pattern "^admin$" ensures complete case-sensitive string match;^means the beginning of string and$means its end.If you want case-insensitive comparison, the formula is simply