Forum Discussion
Conditional Colour Formatting. with multiple values.
Let me suggest the following (as long as I understood correctly your problem):
On each locations sheet, set up a list with all the "correct" and valid locations for that specific sheet. Let's say you list your valid locations from B1:B50.
Then, you wish to enter 1 location in A1 and see if that location is within the valid locations to enter in that sheet, which those are listed in B1:B50.
On A1, go to conditional formatting in the home tab. Click on new rule and then on "Use a formula to determine which cells to format". On the designated space, write the following:
=IFERROR(MATCH(A1,B1:B50,0),"Wrong")="Wrong"
And then set the fill colour format to red.
Again, do the same procedure with a new rule and insert the following:
=ISNUMBER(IFERROR(MATCH(A1,B1:B50,0),"Wrong"))
And then set the fill colour format to green.
Hope this functionality suits you well.
Let me suggest the following (as long as I understood correctly your problem):
On each locations sheet, set up a list with all the "correct" and valid locations for that specific sheet. Let's say you list your valid locations from B1:B50.
Then, you wish to enter 1 location in A1 and see if that location is within the valid locations to enter in that sheet, which those are listed in B1:B50.
On A1, go to conditional formatting in the home tab. Click on new rule and then on "Use a formula to determine which cells to format". On the designated space, write the following:
=IFERROR(MATCH(A1,B1:B50,0),"Wrong")="Wrong"
And then set the fill colour format to red.
Again, do the same procedure with a new rule and insert the following:
=ISNUMBER(IFERROR(MATCH(A1,B1:B50,0),"Wrong"))
And then set the fill colour format to green.
Hope this functionality suits you well.