Add tick mark at the end of text in a cell with conditional formatting

[sharshra]

I´m using conditional formatting in a range of cells to indicate the tasks which are completed in green text. In additional to turning it to green text, I want to add a tick mark at the end of the task based on the same conditional formatting. I know to insert a tick mark in a text, add a tick mark in a cell based on the value in another cell etc. I searched internet, but couldn´t find a solution to add tick mark at the end of the text in a cell using conditional formatting. Can experts in this forum help please?

Consider the sample tables below.

Table 1 has the conditional formatting where the text in all columns in a row turns green when status is complete. This is simple conditional formatting & works correctly.

Book1
	B	C	D
2	#	Task	Status
3	1	Task 1	In progress
4	2	Task 2	Complete
5	3	Task 3	Complete
6	4	Task 4	In progress
7	5	Task 5	In progress
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:D7	Expression	=$D3="complete"	text	NO

Table 2 shows what I need with a tick mark added at the end of the task when the status is complete. This is in addition to turning the text green in all columns green in a row where status is complete.

 

[awoohaw]

I don't think a worksheet formula/formatting solution is possible. Do you want a VBA solution? This can be done with a worksheet change event.

 

[Eric W]

It can be done with a custom number format:

Set up the CF rule formatting by going to the Number tab, and creating this format:

I got the check mark by using the formula:

=UNICHAR(10003)

Then copied the cell, and did a Paste Special-Value, then I could copy/paste it into the Type: section after [Green] @

Also note that you don't have an option of the particular shade of green, you would have to adjust the formatting in columns B and D if you want them to match.

 

[sharshra]

Thanks for the suggestion, Eric. But I think I´m looking for slightly different solution. When I enter `complete´ in the status column, text in all columns in that row should change to green text. This is the first part which is simple to do in conditional formatting & it is working well. 2nd part is to add a tick mark to the text in the `task´ column & I´m struggling here. How can I use the conditional formatting to add a tick at the end of the text in that row? Does the custom format check the condition in another cell & add a tick mark at the end of the text in another cell?

 

[awoohaw]

Thanks for the suggestion, Eric. But I think I´m looking for slightly different solution. When I enter `complete´ in the status column, text in all columns in that row should change to green text. This is the first part which is simple to do in conditional formatting & it is working well. 2nd part is to add a tick mark to the text in the `task´ column & I´m struggling here. How can I use the conditional formatting to add a tick at the end of the text in that row? Does the custom format check the condition in another cell & add a tick mark at the end of the text in another cell?

I'm not sure I understand why you think @Eric W's solution will not work for you? You just need to amend it a little bit. He has given you an idea of how to use conditional custom formatting for text in any cell. You just need to create two custom formats one without the check mark, and one with and apply to the same conditions for different columns.

 

[Eric W]

Like awoohaw said, you'll need 2 rules:

Book1
	B	C	D
1
2	#	Task	Status
3	1	Task 1	In progress
4	2	Task 2 ✓	Complete
5	3	Task 3 ✓	Complete
6	4	Task 4	In progress
7	5	Task 5	In progress
8
Sheet7

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C:C	Expression	=$D1="Complete"	text	NO
D:D,B:B	Expression	=$D1="Complete"	text	NO

I used pictures originally, since xl2bb didn't show the shading in column C, or the special number format. But use your existing CF formula in columns B and D, and the one I provided in column C. It's not 100% perfect, because of the shading, but if that's important, awoohaw can help you with a macro that will trigger whenever the data in column D changes.

 

[sharshra]

Perfect, @Eric W. You made it clear. It works like a magic Thanks a ton for your help.

 

[Eric W]

Glad we could help!