conditional formattingformulasgoogle sheetsgoogle-sheets-dates
I am new to Google Sheets and trying to create a custom formula in conditional formatting to make the fill colour of a cell change based on another cell containing a specific text.
I want to change the colour of cells in column D to grey if cells in column C contain the text "Saturday" or "Sunday".
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).
For partial string matches, use regexmatch, such as
=regexmatch($A:$A, "foo$")
to match any string ending with "foo".
Aside: I find it easier to enter conditional formatting rules as they apply to the single cell in the upper left corner. So my version would be =regexmatch($A1, "foo$"). If the formatting rule is applied, say, to the range C:F, then for E3 the formula would be automatically remapped to =regexmatch($A3, "foo$")
Best Answer
A custom formula in the Conditional Formatting interface can do this for you. It looks like your dates in Column C are formatted as dates, and not just text. So I'll demonstrate a formula that will work in that case. (If your Column C is just text, you can adjust the formula to look for the strings "Saturday" or "Sunday".)
Select Cell D13. Click on the Fill Color button. Select the
Conditional Formattingoption. In theApply to rangebox, enter the range you want the condition to apply to. From what I can see in your screenshot, that would be D13:D20, and probably more. In theFormat rulessection, pickCustom formula is. Then enter this in the custom formula box:=Or(Weekday(C13)=1,Weekday(C13)=7)Then under
Formatting style, select the Fill Color button, and select the shade of grey you prefer. Then clickDone.Explanation of the formula
The formula will inspect cell C13 (and it will adjust as the range continues downward). It will determine the day of the week that C13 represents. If it is Sunday (1) or Saturday (7), the condition will be true, so the conditional formatting will apply.