Comparing two columns with variable ranges [closed

Question

I have a reference Column in excel(suppose Col:A). the new data is entering in Column C. I want whenever a user enter data in Column C after leaving each cell in Column C the code check the value.

        Column A                   Column C
       ------------------------------------
          1001                     2000  
          2000                     1005
          1005                     2000

if the value is Column A the background changes to blue (here 1005 and 2000)

if the value is duplicate in Column C the background changes to red. (here 2000)

I know I can get a value in a cell exists in range like this

         =COUNTIF(A1:A140;C1)

but I don't want to repeat it for every cell , because neither the number of cell in column A is distinguish, nor in column C.

Answer 1

First select column C then use Home ► Conditional Formatting ► New Rule. Once the New Formatting Rule dialog is open, choose Use a formula to determine which cells to format and supply the following for the Format values where this formula is true: text box.

=countif($a:$a; $c1)

Click Format and select a blue Fill. Click OK once to accept the new format then OK again to create the new rule.

With all of column C still selected, repeat with =countif($c$1:$c1; $c1)>1 and a red Fill.

Your results should resemble the following.