This effect is easy to create with conditional formatting.
To start off, I’ll delete the existing conditional formatting rules and then rebuild them step-by-step.
Now, to make things easier to explain, I’ll create a set of dummy formulas for testing.
Let’s do the country first.
K5 needs to be an absolute reference so that it won’t ever change.
B4 needs to be a mixed reference, with only the column locked.
This is because we only want to test the cell values against the country values in column B. OK so next, let me extend the formula to handle the month.
The OR function is a perfect way to handle this.
I can use the existing expression as-is, then add a second expression.
For month, we need
=B4=K6
As before, I need to lock some references.
When I copy the formula across, all cells associated with Korea and April return TRUE.
If I change either value, the formulas recalculate as needed.
What if I just want to highlight the lookup value only?
Well, that’s easy to do.
I just need to replace OR function with AND function to enforce both conditions.
To demonstrate, I’ll add a second rule to highlight the lookup value in pink.
So it’s possible for you to see that both rules work nicely to highlight the lookup values.
And since we don’t need them anymore I can just remove the dummy formulas.
