0

Hello,

I am trying to use an if function to color code cells. I am ok with using Conditional formating also, but I can not find the correct formula/function to make it work how I want.

I am trying to color code a cell if the value of another cell is the word "Complete".

Example.

Cell B15 is a date.
Cell D15 is a word (either complete, in progress, or not complete)

If D15 is the word "Complete" then I would like Cell B15 to turn Green. Either using conditional formating or a function formula.

Any suggestions?

2
Contributors
3
Replies
4
Views
9 Years
Discussion Span
Last Post by Custofus
0

You will have to combine them:

1. Use an IF function to find out if the other cell contains "Complete"

2. Have the Then and Else portions of the IF function place different values in the current cell.

3. Use conditional formatting to select the color.

4. If you don't want the values you put in the cell to show, let the conditional formatting set the text color the same as the background color.

0

I just noticed that you said the cell to be changed contains other data. The above will not work if the other cell is to display a value not related to the color displayed.

- Conditional formatting works on the value of the cell itself, not the value of another cell.

- If always replaces the current value of the cell.

I have two suggestions:

1. Put some text in with the date to be displayed in the IF function (assuming the date is not just entered - it must be a calculated value). Then look for the text in the conditional formatting.

2. Use a macro.

0

I just noticed that you said the cell to be changed contains other data. The above will not work if the other cell is to display a value not related to the color displayed.

- Conditional formatting works on the value of the cell itself, not the value of another cell.

- If always replaces the current value of the cell.

I have two suggestions:

1. Put some text in with the date to be displayed in the IF function (assuming the date is not just entered - it must be a calculated value). Then look for the text in the conditional formatting.

2. Use a macro.

FYI I found the solution to the problem. Using conditional formating. The format looks like this:

=LOWER($D15)="complete"

That makes the cell (B15) change to the color i want when typing in the word complete (in D15). Hope it helps someone else sometime.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.