Member Avatar for Custofus

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
  • 166 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by Custofus
Member Avatar for MidiMagic

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.

Member Avatar for MidiMagic

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.

Member Avatar for Custofus

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.