• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional formatting if cell value matches any value in range of cells

jb

Member
Hi experts,
I have numbers 1 to 360 in column A of sheet 1. 0ther columns contains some data.
Now I have one another sheet - sheet 2 which contains a column j having some selected values
like 1, 4, 7, 18 etc. These values are between range of 1 to 360.
Now I want to highlight values of column A of sheet 1 if it matches any value in the list of column j of sheet 2.
Please help.
 
Hi ,

Let us assume the following :

1. Your numbers 1 through 360 in column A of Sheet1 , are in the range A5:A364.

2. Your numbers 1,4,7,18 etc., are in the range J5:J100 , in Sheet2. Name this range Check_For.

3. Select the range A5:A364 , click on Conditional Formatting , click on new Rule , and enter the following formula :

=ISNUMBER(MATCH(A5,Check_For,0))

Select any Fill Colour of your choice.

Narayan
 
I would prefer using the if & vlookup formula and naming the Sheet2 range

Do the following,
1. Name the Sheet2 range as name it as 'rng'.
2. Select the Sheet1 range and goto the conditional formatting option and use the below formula:
=IF(VLOOKUP(A1,rng,1,0)=A1,1,0)

Hope this helps..
 
  • Like
Reactions: Apo
Thanks a lot, I've used the second method =IF(VLOOKUP(A1,rng,1,0)=A1,1,0) and worked like a charm.
 
Back
Top