Can I use conditional formatting in Excel 2010 to highlight cells according to a date found in two other columns?

Answer 1

Conditionally highlighting cells in one column based data in another column can be easily accomplished using Excel’s Conditional Formatting functionality.

For example, assume you have a list of event dates in column ‘A’ and you want to highlight the corresponding event descriptions in column ‘B’ if the date in column ‘A’ occurs before 11/02/2019.

Since the conditional formatting formula requires that dates used directly in the formula be in the Excel “serial” date format (Excel stores dates as sequential serial numbers, with January 1, 1900 being serial number 1, because it’s more efficient for use in date calculations), you’ll want to convert 11/02/2019 to this format. To determine the Excel serial date of 11/02/2019, input this value into any cell in your worksheet and then change the format of that cell from ‘date’ to ‘number’. The Excel serial number 43771 will be displayed in this cell because it is 43770 days after January 1, 1900 which is serial number 1.

To create the conditional formatting formula, first highlight column ‘B’. Then navigate to the Excel Ribbon, select Conditional Formatting then “New Rule” then “Use a formula to determine which cells to format". In the formula box input =$A1<43771. Alternatively if the date 11/02/2019 is located in cell C2, the formula can be written as =$A1<$C$2. Next choose how you’d like to format cells that meet this criteria for example by filling the cells with a light green fill and displaying text in an italic font style. 

The event descriptions in column ‘B’ with corresponding dates in column ‘A’ which occur before 11/02/2019 will now be formatted with a light green fill and an italic font style.