Forum Discussion

dlopez20's avatar
dlopez20
Copper Contributor
Aug 23, 2023
Solved

Conditional Formatting Using an entire Column

Hello, 

 

I have a list of dates on one sheet, for example:

12/2/2023

1/6/2024

1/20/2024

2/19/2024

3/5/2024

 

and a list of Holidays and other exclusion dates in another sheet:

12/25/2023
1/1/2024
1/15/2024
2/19/2024
5/27/2024
6/19/2024

 

I'd like to have the first sheet of random dates to be formatted so that if it matches with one of the dates on the second, Holiday/Exclusion sheet, it turns a specific color. 

 

Is there a way to create a conditional format rule using the entire second sheet Holiday column? I am trying to avoid entering the rules individually for each Holiday/Exclusion date as there are a lot. I have tried A:A or A1:A80 and get the error "Change the reference to a single cell, or use the reference with a worksheet function, such as =SUM(A1:E5)".

 

In this case, the rule should format the 2/19/2024 date in the first sheet since it is listed as a holiday in the second sheet.

 

Thank you.

 

excel

5 Replies

      • mtarler's avatar
        mtarler
        Silver Contributor
        Aug 24, 2023

        dlopez20 not sure why. In the attached I give an example show it works and since I included a fun/handy HOLIDAY TABLE I added another version:

        this holiday table calcs the holiday for each year you enter under YEAR and 'included' the holiday if indicated in row 1:

         

Resources