find match and replaced.

J

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
 
M

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
 
J

John

Max,
thanks for the help.....

Max said:
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
Click to expand...
 
Top