I am trying to conditionally format a cell that is green for dates in the future (from today), red for dates in the past (before today) and black for two criteria, 1) date in the past and 2) when a cell has been marked as 'complete'. The first 2 work ok, but I can't get the black conditional format to work. Any ideas
Hello Claire,
Thank you for the detailed explanation of the problem. I understand you wish to apply another condition with 2 criteria. To create this combination of conditions, you can use a formula that combines the two conditions using the AND function. Follow the below instructions:
Initially, select the cell or range of cells to which you want to apply the Black formatting.
Further, go to the Conditional Formatting> New Rule.
Afterward, choose the option Use a formula to determine which cells to format.
In the formula box, enter the following formula:
=AND($A$1<$B$1,$C$1="Completed")
This formula checks if both conditions are true: if the date in A1 is in the past, and if the value in column C for the same row is Completed.
Later, format the cell with Black color and text as White. I have attached the Excel file for better understanding.
Note:
Do not forget to put the conditional formulas sequentially.
Here, I have taken 3 ranges separately to illustrate the conditions in one image. Select the whole range in your case.
