Apply conditional formatting to column A when date is entered in column B (adjacent cells)

I have a spreadsheet that has three conditional formatting rules, and I would like to add a fourth/final rule as follows:

When a date is entered into a cell in column B, the adjacent cell in column A should go from a red, orange or yellow background to white/no background.

To explain, there are projects being entered into this spreadsheet with the target completion dates column A. Column A is conditionally formatted based on the date from "TODAY" as either red, orange or yellow, depending on its proximity to today's date/the date that the spreadsheet is opened.

However, once the project is complete, the user enters the date it is completed in column B, and the formatting in column A should then automatically return to a white background.

I am having trouble figuring out how to format this. Thank you

This did not work. Below are my steps - did I miss something?

I highlighted column A (since this is where the formatting will be applied)
Went to "Conditional Formatting"
Selected "New Rule"
Selected "Use a formula to determine which cells to format"
Entered: =$B:$B=Completed
Selected "No Color"

the formula should be:

=$B2=Complete_date (Complete_Date is a reference to some cell containing a date) e.g =$b2=$Z$100 ($Z$100 has completion date)

Then set "Applies to" the required range e.g B2:B10000 (NOT whole column)

I am sorry, but it is not working when I use: =$B2=Complete_date

Also, isn't the range in column A?

When I enter a date in cell B2, then the date in cell A2 should go from being a color to white. When I enter a date in cell B3, the date in cell A3 should go from being a color to white.

A B
row TARGET COMPLETED
2 01/2/2018 (say this is CFd yellow) 12/28/2017 (now that this date is entered, the CF in A2 should turn to white)
3 11/1/2017 (say this is CFd orange) 12/30/2017 (now that this date is entered, the CF in A3 should turn to white)

When a date is entered into column B for the particular record identified by row, column A in that same row recognizes it by changing the cell color.

This seems so easy, but I am apparently missing something.

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

I have attached a sample spreadsheet. Thank you!

Thank you so much for this formula. It works in my sample spreadsheet, however, it does not work in my original spreadsheet and I will try to explain what I found after troubleshooting.

Column B contains Target Dates and Column C contains Completed Dates. Each row contains an individual project - one row one project.

When I use this formula in my original spreadsheet, this is an example of what happens:

Cell B2 contains a Target Date, which once I enter a Completed Date in cell C2, the conditional formatting for Cell B2 should change to white/no color.
Cell B3 contains a Target Date, which once I enter a Completed Date in cell C3, the conditional formatting for Cell B3 should change to white/no color.
Cell B4 contains a Target Date ...

Using the formula you provided, when I enter a completed date in cell C2, nothing changes in cell B2. However, if I enter a completed date in cell C6, for whatever reason that date entry is recognized and changes cell B2 to no color/white. Because cell C6 is a different project, it should not affect cell B2, only cell C2 should prompt the conditional formatting change.

I am not sure why this is happening, but any assistance would be greatly appreciated.

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Still doing the exact same thing - it is working, but looking at the wrong row. So, I enter a date completed into cell C23 and it changes the conditional formatting to white in cell B18 when it should be changing the status for cell B23 (next to cell C23). Any thoughts?

I did not see any information in your reply, and thought that perhaps my sample spreadsheet did not properly attach so I am sending it again. Thank you

As requested, post your file. I suspect the problem is that referred to in post #13.

EDIT: file posted!

I replied actually stating that the advice in post #13 did not work - it is still reading the incorrect row. Any additional thoughts as to why?

Your sheet works: put =TODAY() against any highlighted column B and the colour is now "no colour"

Delete the dates and the colour is re-instated.

What do you mean put =TODAY() against any highlighted column B ?

Enter Today's date in C) just to trigger the CF.

What does the actual formula look like then?

Just enter any date ( or NUMBER) in a cell in column C

I don't understand - when I enter a date in a cell in column C, the adjacent cell in column B does nothing.

This is what I have in terms of a formula per your suggestion in cell B2

ISNUMBER(C2)

with "No Color" as the formatting.

Do I need to change any of this?

See attached row 12 (as example)

C12 has =TODAY()

It is definitely working in this sample spreadsheet, but not in my working spreadsheet that is much larger. I have edited/scrubbed my original which has many more rows. I am looking to see if there is a formatting issue that I am overlooking, but am also attaching it to show you in case you can help me.

See attached with corrections applied as per Aardigspook's post

Thank you for the sample that you mocked up. The formulas are working in that spreadsheet, but not when I enter it into my original spreadsheet - ugh!

I have gone through the original spreadsheet removing formatting and redoing it, etc., and nothing seems to work. There has got to be something embedded in it that is keeping the last condition (completion date) from working.

I don't know if you have any ideas about this, but I am going to keep trying to figure it out.

Thank you

Select the first cell in column C that has the date e.g C4. then in CF

formula

=ISNUMBER($C4)


FORMAT...FILL.."No Colour"

then set "Applies to" to $C$4:$C$1000 or whatever your range is.

Column B is the column with the Target Dates that are conditionally formatted based on their distance from "TODAY"'s date.

Column C is the column with the Completion Dates in which once a completion date is enter, the target date in column B should now have "no color."

Are you saying that I should be conditionally formatting column C (completion date) with a formula to remove the color in column B (target date) instead of the other way?

omg - I think the problem is that I am assigning "no color" to the formatting. I tried using a light blue, and it seems to be working in my original spreadsheet.

Unfortunately that is not the issue, because I have actually clicked the "No Color" option in that screen. That said, it works in the sample, which is great, but not in my production sheet but the light blue is a decent option. Thank you so very much for your great help!