Expert Solution - How to Apply Conditional Formatting Based on Another Cell

Question description:
This user has given permission to use the problem statement for this blog.

I need help with conditional formatting. I would like to highlight cells based on a "yes/no" value rather than numeric digits.

Solved by V. J. in 16 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert 08/08/2018 - 08:41

Welcome to Got IT Pro.

Excelchat Expert 08/08/2018 - 08:41

Please share the sample info.

User 08/08/2018 - 08:41

Hello

Excelchat Expert 08/08/2018 - 08:41

You want to highlight the rows based on Yes/ No?

User 08/08/2018 - 08:42

That is correct

User 08/08/2018 - 08:42

Can I provide a quick example on the spreadsheet screen?

Excelchat Expert 08/08/2018 - 08:42

Can you please provide sample info in the preview?

Excelchat Expert 08/08/2018 - 08:42

So, i can apply conditional formatting based on it.

User 08/08/2018 - 08:42

Of course, one moment please

User 08/08/2018 - 08:44

If I wanted to highlight the individual's name based on the yes/no rather than than say payment deposit, how would I do that?

Excelchat Expert 08/08/2018 - 08:45

Ok. I can highlight Yes with green and No with Red?

Excelchat Expert 08/08/2018 - 08:46

Please check it.

User 08/08/2018 - 08:46

That is correct (sorry, forgot to mention)

Excelchat Expert 08/08/2018 - 08:46

=$C1="Yes"

Excelchat Expert 08/08/2018 - 08:46

Here is the conditional custom formula that is applied.

Excelchat Expert 08/08/2018 - 08:47

And for No, =$C1="No"

User 08/08/2018 - 08:48

Would it always start with =$C1? If my data table started at row 7

User 08/08/2018 - 08:49

Would that mean I start with =$C7?

Excelchat Expert 08/08/2018 - 08:49

Yes that is right and applied range also starts at A7:C1000

Excelchat Expert 08/08/2018 - 08:49

Start row is also 7 in the applied range.

Excelchat Expert 08/08/2018 - 08:49

You can change 1000 to any number you wish.

User 08/08/2018 - 08:50

That makes sense

Excelchat Expert 08/08/2018 - 08:50

Hope you are able to achieve this.

User 08/08/2018 - 08:50

Can you please guide me through this one more time? I wanted to see where you clicked to apply the conditional formatting. It didn't show up too well on my end.

Excelchat Expert 08/08/2018 - 08:51

Hope, you are using Excel and Not Google sheets?

User 08/08/2018 - 08:51

Yes, I am using excel

User 08/08/2018 - 08:51

There is a conditional formatting function

Excelchat Expert 08/08/2018 - 08:51

In Home menu, there is conditional formatting.

Excelchat Expert 08/08/2018 - 08:52

Please select the range first before clicking on the conditional formatting.

User 08/08/2018 - 08:52

I was trying to use the "highlight cell rules"

Excelchat Expert 08/08/2018 - 08:52

And then choose new rule or custom formula

User 08/08/2018 - 08:52

Would the range be the column itself, or the entire table?

Excelchat Expert 08/08/2018 - 08:52

You have to use custom formula. As Yes / No is in column C and highlight is from A to C.

User 08/08/2018 - 08:52

It looked like it was the entire table when you did it here

Excelchat Expert 08/08/2018 - 08:53

I have selected A to C.

Excelchat Expert 08/08/2018 - 08:53

And custom formula.

Excelchat Expert 08/08/2018 - 08:53

In you case, you have to choose from A7 onwards.

User 08/08/2018 - 08:53

Ah, ok

User 08/08/2018 - 08:54

I think I was selecting the wrong "range" the entire time

User 08/08/2018 - 08:54

And also forgot the quotation marks around the value

Excelchat Expert 08/08/2018 - 08:54

Ok. Based on your selection, the formula will change.

Excelchat Expert 08/08/2018 - 08:54

Ok. Yes and No are strings.

Excelchat Expert 08/08/2018 - 08:54

So, you have to apply double quotes.

User 08/08/2018 - 08:56

Thank you so much for taking the time to teach me. I really appreciated it!

User 08/08/2018 - 08:56

You're a good teacher :)