I'm building labor tracking spreadsheet that has every day of the year in it. Some of those days are paid vacation days. I would like to highlight the rows of those particular dates (not a range), in order to make certain the hours entered in those rows stand out (like in green, or whatever). I have a list of holiday dates (in long date format). I would like to use that list in order to find those dates in my main date column (also all displayed in long date), and then highlight the rest of that row. Help would be much appreciated
2
-
If possible share some sample data especially list of holidays you are suppose to match with Date Column to Format, help us to fix the issue.– Rajesh SinhaCommented Feb 5, 2019 at 5:14
-
Also, please add a screenshot because it isn't clear what the worksheet looks like.– fixer1234Commented Feb 5, 2019 at 6:04
Add a comment
|
1 Answer
How it works:
- Select the list of Holidays, including header (Dates, Check Screen Shot).
- Press Ctrl+Shift+F3.
- From
Create Names From Selection, check theTOP ROW& finish with Ok. - Reach to
Conditional Formatting Commandand hitNew Rule. - Write this Formula.
=COUNTIF(Holidays,$A2)>0
N.B. In the Formula, Holidays is the Name Excel assigned to Holiday's Date list.
- Apply an appropriate Format & finish with
OK.
You find the Dates are now highlighted as matched with list of Holidays (Dates).
Adjust cell reference and the Holidays list as needed.
answered Feb 5, 2019 at 5:39
