Written by Allen Wyatt (last updated September 30, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365
Ronald has a worksheet with 365 or 366 rows, one for each day. The date is in column B. Using conditional formatting he can highlight the current date, but Ronald wonders how he can highlight the whole row for the current date.
This is relatively easy to do, provided you know a trick in setting your conditional formatting rule. Let's say that you have your data in the range of A1:T367, with the first row being used for column headings. You need to select all the rows that may have dates in them. So, in this case you would either select the range A2:T367, or simply select rows 2 through 367. (Either selection will work just fine.) Now, follow these steps:
Figure 1. The New Formatting Rule dialog box.
=$B2=TODAY()
The "trick" I mentioned earlier is in step 5, when you define your formula. Remember that you started out by selecting a large range of cells or a large number of rows. That selection determines the cells to which the conditional formatting rule will be applied. However, the formula indicates that only the values in column B will be taken into consideration when evaluating the rule. This is the purpose of the dollar sign ($) it indicates an absolute column, one that doesn't change. Thus, all cells will be formatted when the formula is true, meaning that the date in column B is equal to today.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13389) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
Conditional formatting can be used to highlight cells that contain the improper type of data for your needs. This tip ...
Discover MoreWhen you paste information into a row that is conditionally formatted, you may end up messing up the rules applied to ...
Discover MoreNeed to know if a particular cell contains a date value? Excel doesn't have a worksheet function to determine this ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments