Hi there. In this video of conditional formatting, formula based conditional formatting, let's take one case study, a case study, which I recently came across when a client asked me this question. He asked me that look, they have a restricted list of ID codes. And if one of the staff members enter any ID code, which happens to belong to the restricted ID code, it should get colored automatically. So that they know that they are entering an ID which is already restricted or in the blacklist. Now, it looks something like this.
In this case, 19 52,013 both of them are in the restricted Id also 3000. So if I write 3000 colors, see thousand 10 no color, because of course that is not there in the restricted ID list. Now to be able to achieve this, you will require two things one is count if formula and conditional formatting. Let me quickly walk you through the count. If formula In case you have not been using in recent times, what count is does is when you write count, if it asks you primarily two things, one is the criteria range that is, in which range, which I'm going to fix at this moment, you are looking for a certain criteria. In this case, it is talking about this 1950.
So, primarily 1950 is being searched in this list and if it is present, it will tell you how many times does it appear. So, this logic I'm going to put in the formula based conditional formatting and find out if there is any value whose count is more than zero, which indicates it's going to be there in the restricted list. So now I choose the entire area where I'm supposed to have that condition filled in. I go to Home tab, I click on conditional formatting. Next, I go to new rule. And towards the last option, which allows me to put a formula in the conditional formatting, I will go to that section.
So use the formula to determine which cells to format. And that's where I write the formula whose answer is going to be either true or false. Now notice my selection of the cells had begun from two seconds. Hence, I'll be careful when writing the formula I say equal to count ifs. And the worst part is you don't get any hints, when you open up the parenthesis or brackets, you have to remember what was the next parameter. So counters, it asks you criteria range.
So I say, let me choose the industry in which restricted list exist. Already It is been locked, which is good for us that we put a comma and I will choose only one cell that is my criteria, not the cell above, not the cell below, not the entire range, that one cell from where my selection had begun. And also, before you go ahead, make sure that this is locked in such a manner that you are Allow conditional formatting to move down but not sideways, which means dollar D, I keep on pressing f4 until I get dollar D to close the bracket. But notice this is only going to give you an answer in digits 01234 and so on so forth. I don't want that I want an answer which should exhibit true or false. So I asked is discounted answer whatever that is greater than zero which means is it there in restricted idealist?
Because only then it will give you an answer more than zero. So if it is so, in which case I will get an answer to and because of which this condition formatting gets activated. I go to Format and decide on my color. So that there you go, a light blue color will do Okay, okay. Nothing gets applied, I write 1950. And proof of the pudding is in the eating there you go enter and for instance, I apply a number which is not there.
In the list, it doesn't get colored, but that does affect the client had wanted. And you can use the same technique to give your condition based on counties formula