Hey everyone. Oh, actually, hi Rashab. But if everyone is watching this, then Rashab said it's okay to share with everyone that is a better Sheets member. So Rashab, can I share this with everyone? Answer the question, but I need to answer your question. Your question is, you have these events and dates and statuses.

And you want to turn them green actually you wanna turn them any, some color, the whole entire row based on if in this D column the status is closed and me see is closed by someone. So we're getting this data from some function or somebody's writing in closed by this person, or we're getting this some way somehow, but the what we wanna do is, right.

We want to not only. Condition we're gonna use conditional formatting we're we don't want to just do a single cell, we wanna do the entire row. Cause that really makes it a really. Easier to see that this is green or red, or whatever color you want, we can choose. The answer is we use this custom formula and conditional formatting and I'll go through step by step how to get here.

We use the entire range A to D to get the entire row all the entire rows. We use this search and then we use this dollar time. That's very important. And so I will step through all of this from a blank sheet. Go here. Let's make a blank sheet. I'm just gonna paste the, that's your name. I want to actually paste just this data here, just so we start with fresh.

Okay. So we have this some way somehow, and it doesn't oh wait, I did the start date there. Let me just clear that formatting. Okay, here's here's the data we get. We have these dates, we have this status that has some phrase, but we really just care about that closed and we have the event name, so we're gonna go up to format.

We're gonna go to conditional formatting, and the conditional formatting we are going to get is this format sells, if not empty. We get the screen, we get this D five. First things first, let's change the format and we're gonna use a custom formula and we're gonna search what are we gonna search for?

We're gonna search for here, let me go back and look at the formatting, because sometimes we there, it's closed, and then we search for closed. And where do we search for it? That's the format. Okay, let's go back here. So what do we want? We want search. What do we want? We want clothes. When do we, where do we wanna find it?

In D five. Okay. But we're applying that rule to this D five column. Okay. So where do we actually want to apply? We wanna apply the entire sheet. So we're just gonna do, I'm gonna do A to D so we get there. But you can change this to z a. I'm gonna do date D right here. Okay. And we see we now have this, the colors over here that's going to be easy to fix.

How we fix that is over here. We ha we can add a dollar sign. And what that means is that no matter where this cell is and this is why this happens and why we need. Condition formatting cares about the cell and applies this rule? Depending on the cell, it's going to change this rule of D five, so we don't want it to change the column.

We always want it to look at the D column of this row, and we actually do want it to change based. This event is with this closed. This event is with this closed. We do want to app change the conditional formatting based on each of these rows. And you see these three are highlighted. It's because this, A one is looking at D five here and then this A two is adding one to this five.

It's not changing the D, so it's looking at the second. And this third green, this a three is changing this five to D seven, right? So how do we get that to not be like crooked, right? Like this A one is looking at D five. It's because we wrote D five here. If we wrote D one, suddenly A, they align because this one is actually changing based on the row.

So for A one, it looks at D one for A two, it looks at D two. A one always looks at d, b always looks at d. C always looks at d. D always looks at D because we've put this dollar sign, which is like a hole. It like holds that D no matter where you are in the sheet, it holds that D And we always look at the D column and it really, the magic is here and how we apply to that range, right?

So that we get this entire row right. The. Conditional formatting, how it shows up. It shows up like this, and it really tricks us and it makes us think there's no really way to make it apply to the entire sheet. But we can, we just do this. And now we can apply also to, let's just do e we can change.

And now that E is also looking at D because we put this dollar sign here. And so hopefully this custom formula here, this search you can use any formula. Google has, it's really very powerful. It allows you to do so much business logic without having to actually create a column that like, basically you can say is this number divisible by three?

Make it red or is it out? Or even we can do that here with this, these custom formulas. It makes really powerful what you can do with this conditional formatting. One thing of note though, that I try to remind people that use conditional formatting is that it slows down your sheet a lot.

The more you use conditional formatting, the more range it is. The more it does the more ranges you apply. If you have multiple conditional formatting rules on multiple sheets it's going to look at those form conditionals. Every time you edit and it re that is, it was the number one thing that really slowed down our sheets.

It wasn't when I worked on sheets for an organization, it was not the amount of stuff that was in a sheet. It was literally the number one culprit was conditional formatting. So just. Be aware of that as you use this condition formatting. But hopefully this answered your question of how do you search for a single word within this cell and apply a condition forming for the whole thing.

Also, let's go back. I wanna actually just double make sure, double check that you know, this is, you can then change the color down here. You can change it to orange. Green. Green. I thought I clicked on orange, green. This orange. Yeah. There you go. And it'll apply it here. If you want to look for different rules, make sure those or the order of the rules are in the order that you wanna look for them.

Sometimes like here, let's see. We want this actually is something that might happen. You're like, oh, I want to color everybody differently. But if they have clothes, I want everyone with clothes to be orange, right? So we go, same thing. Search for this person D five and I'm gonna put that dollar sign there.

And now we we don't do D five DT D one because our range starts with one here. And when you say yellow, oh no, not orange. We want yellow for mamata mata. And we. And we don't see that. What happened to our conditional formatting? What happened? Oh no, I can't show you what I'm trying to do.

What happened? I think I know what happened. Did I? There it is. Okay. It's only in eight. I dunno why it does that. Okay, so we're looking Oh, cuz it's a v e. That's why I didn't do that. Why does it not change to yellow? Because it first does this and then this. If oh my God, can't, if I click and drag it, boom, we got yellow here because conditional formatting rules go in order that they are listed, so we can move them around.

But just know you. You want everyone who is closed to have orange, but if it's not closed, but it has their name, everybody has different colors. This is what you don't wanna do. You want to put the closed at the top and that's how you do that. Hopefully that's helpful. Bite.