I’m working with a Google Sheets document where column B contains various dates (like B1: 2013-12-15, B2: 2013-12-16, B3: 2013-12-17, and so on). What I want to achieve is automatic row highlighting that changes the background color for whichever row contains today’s date in column B. This way, each day a different row would be highlighted automatically.
I’m pretty sure this will require some kind of script since I don’t think regular conditional formatting in Google Sheets can handle this type of dynamic highlighting based on the current date.
Does anyone have suggestions on how to implement this? I’d really appreciate any help with this problem.
pete’s right about conditional formatting, but watch your range setup. I ran into this exact problem last month - kept screwing up the formula range. First, select your entire data area (A1:E100 or whatever you need), then add the rule: =$B:$B=TODAY(). That dollar sign before B matters - it locks the formula to column B when it applies across your rows.
Had the same problem with my team’s deadline tracker. Conditional formatting works well, but your dates in column B need to match TODAY()'s format. Mine were formatted as text at first and nothing highlighted. Heads up - if you’re sharing across time zones, TODAY() uses the sheet owner’s timezone. Threw off my international colleagues until I added a note explaining it. The highlighting refreshes automatically when you open the sheet, so it’s solid once you get it set up right.
You don’t need any scripting for this - Google Sheets’ conditional formatting handles it perfectly. I’ve used this setup for months on my project tracking sheets. Go to Format > Conditional formatting and create a custom formula rule. Use =$B:$B=TODAY() and pick your highlight color. Apply it to your entire data range, not just column B. TODAY() updates automatically each day, so the highlighting moves to whatever row has the current date. No manual work needed. I prefer this over scripts because it won’t break from trigger issues or permission problems in shared docs.