Problems with Conditinal Formatting

[fireguard]

Greetings

I have a list of numbers on a worksheet for example on sheet 'NUMBERS'
On another sheet I enter a number in column A (example 1234) and column B performs a check if that number is in the list on sheet 'NUMBERS'.
If the number 1234 is on the list the cell in B column performs conditional formatting and changes with a green fill.
If the number 1234 is not on the list, then the cell in column B changes with a red fill.
HOWEVER,
If at a later stage the number 1234 is added to the list on sheet "NUMBERS', then all the previous red cells turn green, which I don't want to happen.I need the cell that are red to remain red.

If you could assist, that would be champion.

Cheers
fireguard

 

[AliGW]

Explain why they should remain red if there is now a match on the other list. It's not clear what you are trying to achieve.

 

[Special-K99]

Select a cell in NUMBERS! Column B

Conditional Formatting
New Rule
Use a formula to determine...

=ISNA(VLOOKUP(A1,NUMBERS!A$1:A$1000,1,0))
format as Red

=NOT(ISNA(VLOOKUP(A1,NUMBERS!A$1:A$1000,1,0)))
format as Green

Use Format painter (paintbrush icon) to copy to other cells

 

[fireguard]

AliGW
I have a worksheet titled, NUMBERS and on that sheet are two columns, one column titled AA and one titled BB. On this sheet the numbers of items are entered into either one or both lists. So the example of item 1234 could be in list AA, BB or both.

On another worksheet titled MAIN, I have a column titled INPUT and beside that are two columns titled GENERAL and SPECIFIC.

When someone types in the number 1234 in the INPUT cell on the MAIN worksheet, it identifies if that number exists in either AA, BB or both of the lists.

If the number (1234) exists in list AA, then the cell in the GENERAL column fills green. If the number does not exist in list AA, then the cell fills red.
Same thing occurs in the column Specific/

In this way a person can tell which pile the item is to go into ie either pile GENERAL, SPECIFIC or either.

At present, if the number 5678 is not on the list/s, then the cells fill red, indicating that the item is not to be placed into either pile. However, if I was to add that number, 5678, at some later stage, then all the cells related to the number 5678 change to green irrespective of how many times previously the item number was inputted.

I need the cells to remain the color they were originally so that I can track how many times the item was rejected before we decided to accept it.

I hope that makes sense.

Cheers

Special K-99, cheers I will give this a go!