find match and replaced.

[John]

Hi,
anyone know how to find a # in columns F-I and match the # in columns A-C
then replaced that box to empty and fill in red color.
A B C F H I
-------------------------------------------------------
1 6 11 3 12 9
2 7
8 13
4 14
5 10 15

 

[Max]

One simple formulas & CF play which produces
the desired results in an adjacent area

Assume source data in A1:C5,
data to be compared in F1:I1

Put in K1:
=IF(COUNTIF($F$1:$I$1,A1),"",A1)
Copy across/fill down to M5
to cover an area equivalent to the source data in A1:C5

Then select K1:M5 (with K1 active),
apply conditional formatting
using Formula Is: =K1=""
Format > Red fill / white font
Ok out

K1:M5 will return the results that you seek

 

[John]

Max,
thanks for the help.....

One simple formulas & CF play which produces
the desired results in an adjacent area

Assume source data in A1:C5,
data to be compared in F1:I1

Put in K1:
=IF(COUNTIF($F$1:$I$1,A1),"",A1)
Copy across/fill down to M5
to cover an area equivalent to the source data in A1:C5

Then select K1:M5 (with K1 active),
apply conditional formatting
using Formula Is: =K1=""
Format > Red fill / white font
Ok out

K1:M5 will return the results that you seek

 

[Max]

welcome, John.