CODEWITHSUNDEEP
excelvbaexcel-formula
John H
John H

Reputation: 13

Use Conditional Formatting to Highlight dates based on Two Columns data

I'm trying to do something like the image below:

Example of highlighting dates based on date and code I have a list of dates and code and for each date on the list, I would like to highlight the calendar/date that matches the list of date and the highlight color is based on the code associated with the date.

Using this formula,

=MATCH(A6,$K$6:$K$19,0)

in the Conditional Rule Manager will work on just getting the dates colored on the Calendar.

I have tried playing around with the AND statement like so:

=AND(MATCH(A6,$K$6:$K$19,0),$L$6="A")

in the formula. This would work if all I'm looking at is the static column L6 but that's not what I'm trying to do. I don't know how to check the date and the associated code. How do I go about matching up K6 to L6, K7 to L7, and so on?

Upvotes: 0

Views: 951

Answers (1)

teylyn
teylyn

Reputation: 35935

Try Vlookup in the conditional formatting formula. Compare the result of the VLookup with the letter. With cell A6 selected, use this formula

  • brown rule =Vlookup(A6,$K$6:$L$19,2,0)="A"
  • blue rule =Vlookup(A6,$K$6:$L$19,2,0)="B"
  • green rule =Vlookup(A6,$K$6:$L$19,2,0)="C"

Edit after comment: You can highlight all cells that you want to format, but make sure that A6 is the active cell. Or you can select just cell A6, create the formats, and then apply the format to the other cells, using either the "Applies to" field in the Conditional Formatting Rules Manager, or by copying A6 and using Paste Special > Formats to copy only the format to other cells.

I prefer the second method. It's faster.

Upvotes: 2

Related Questions