Comparing input value in a cell to a range of data

[hoongz]

Hi. Im wondering if it is possible to compare a value in a cell to a range of pre defined values. For example the user input a value "apple" in cell A1. Under column B1:B6 are 6 different values. If the value in A1 matches any of the value in B1:B6, it will change the cell A1 to a certain color, and to another color if the value doesn't match any of the value in B1:B6. Is there any way to do it in VBA?

Thank you

[tom1977]

Hi check such event code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) = "A1" Then
If Application.WorksheetFunction.Lookup(Target, Range("b1:b6")) = Target Then
Target.Interior.ColorIndex = 4
Else:
Target.Interior.ColorIndex = 3
End If
End If
End Sub

paste it into module of worksheet

[abousetta]

You can use conditional formatting. Assuming your input is in A1 and your list is B1:B6 then you can use this formula:

=VLOOKUP(A1,B1:B6,1,0)

[hoongz]

Thanks it works. But If i need to make it more complicated, is it still possible. For example, the user needs to choose a category before typing in the value. Each category has its own range of value. E.g, Meat category has the value of chicken, fish, pork, beef. Veg category has value of carrot, potato, spinich under column C. Fruit category has apple, banana, coconut under column D. The user has to first choose a category and then input the value in A1. Is it possible to check the range of possible values based on a category that had been chosen?

[mmmarks]

Hi,
I have used helper column and conditional format. Im not sure whether it will reach your expections .
Look at atttachemnt

[hoongz]

Hi,
I have used helper column and conditional format. Im not sure whether it will reach your expections .
Look at atttachemnt

mmmarks, could you teach me how you make the cell change color as i couldn't see any formula or anything in vba.

thank you

[abousetta]

If you know what the options you want to give people then you can create two drop-down lists and link them. In your situation, if the person chooses "Chicken" then the dropdown menu in A1 would only have the options you want to show them. Data validation at the time of data input is much easier than correcting mistakes later on.

Hope this helps.

abousetta

[hoongz]

Actually the problem is that right now I do know what are the values to provide, but each catrgory have a different number of values to choose from. And I actually need to check against a number of cells because there might be multiple inputs. Im just testing with one input right now by what I aim to acomplish is a sheet where people input multiple data and it is checked against the given value. But the given value might change over time and also the number of values in each category. To give a better understanding, what i need to do is maybe in column A a name input, column B is the input value. There could be multiple records but the good thing is it will only be records for one category. So for example the user have chosen meat, and input 3 records of 3 names and under the input type chicken chicken and potato. But the number of records the user entered each time is different and my set of values might change once in a while when the stock is out or new product is offered. Not sure if I'm confussing people. Sorry if I'm asking for too much.

[hoongz]

Any Advise?

[abousetta]

Can you create a sample workbook with dummy data so we can see how you imagine the final product will look? As for the example by @mmmarks, they are using conditional formatting to change the color if the contents of A1 is the same as B2. The formula is hidden within the conditional formatting function and that's why you can't see it. Click on the Conditional formatting icon on the home tab while cell A2 is selected and you should be able to see it.

[hoongz]

Attached is a sample file. Each time the file should only handle one category but should accept multiple value that falls into that category. As the number of input is not fixed, and the range of each category wmight change over time. Im not sure how can I do a proper validation. Thanks in advance for all assistance

sampling.xlsm

[hoongz]

Anybody understand me or need me to change what im trying to display?

[hoongz]

Anyone able to offer assistance?

[hoongz]

Anyone please

[abousetta]

Hi,

Sorry, been busy. I have looked at your workbook and think you have two options:

1) Instead of using drop-down menus, use data validation instead.

2) If you want to keep the drop-down menus then you can use vba to populate the drop-downs.

http://www.contextures.com/xlDataVal01.html contains a detailed approach to both options. Let me know if you need more help. (the end of the aforementioned webpage has a list of possible scenarios, each with its own set of instructions).

Hope this helps.

abousetta