The range K4:M7 is named “values” for readability and convenience only.
If you don’t want to use a named range, use anabsolute referenceinstead.
The solution above shows matchingdatain green, and matchingvaluesin blue.
The colors are applied automatically with conditional formatting and will update instantly if values change.
This requires two separate conditional formatting rules, each with its own formula.
Normally,rangerepresents the cells being checked.
The relative reference to B4 will change at each cell.
To force a single result, we wrap COUNTIF in theSUM function.
This gives us asinglenumeric result the count of the cell value (B4) invalues.
As above, any non-zero result means that B4 is a number in K4:M7.
And any non-zero result triggers the conditional formatting rule and colors the cell.
Highlight matching values (blue)
The above rule highlights numbers indatathat appear invalues.
We can easily make a rule to highlight cells invaluesthat contain numbers indata.
Each cell invaluesK4:M7 becomes thecriteriaargument inside COUNTIF while therangeisdata(B4:I15).
As above, COUNTIF returns a single count for each cell invaluesand any non-zero result triggers the rule.
