Changing a different cell colour based on dates elsewhere on the worksheet???

Question

Hi there.

I hope someone can help me. This has been driving me mad for days!!

I have columns Q,S,T,U,V,W,X,Y that have certificates expiry dates shown. Within these sections I have I am using conditional formatting. So if a date is past today, the cell turns red. If due to expire within 30 days, it is amber and if past 30 days it is green. This all works lovely.

My question is this. I have column A as the suppliers name and column B as the status. I would like the B cell to change colour based on the results of Q-Y.

So, if any of the dates are current green, the cell stays green and will display a tick symbol.

If any of the dates are amber, the the cell changes to amber with a question mark symbol

If any of the dates are red, the cell colour changes to red with a X symbol.

Alternatively, it changes colour if based on the dates and not the colour the cell has changed to.

If I cannot get the symbol to display, that's not the end of the world.

Does anyone have any ideas?

Please and thank you!!

Regards

Scott

Answer 1

Select B4:B14. B4 should be the active cell in the selection.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula

=MIN(Q4:Y4)<=TODAY()+30

Click Format...
Activate the Fill tab.
Select amber as fill color.
Click OK, then click OK again.

Repeat these steps, but with the formula

and red as fill color. (Sorry for posting a picture, but the forum refused to post the formula as text)

(No symbols)

Answer 2

To add the symbols, you will need to use a formula in Column B if you also want the cells shaded. The alternative would be to use an Icon Set which will give colored symbols but not color the cell. Hans's formulas will color the cell based on the contents of Columns Q to Y. If you add this formula in Column B it will enter the checkmark, X, and at least as close as I could get to a question mark in the cells.

Enter this formula in B4 and fill down (Sorry, you will have to get the formula from the screenshot. A glitch in the forum won't allow the formula to be typed). Then change the Font to Wingdings 2 for all of those cells.

To clarify the formula, the first Value if True is a capital O, the second is a close square bracket, the third is capital P.

Answer 3

I don't know if this below is the solution you are looking for or not.

Table A3:K14 has column D empty and hidden. I created a new table in cells P3:Z14 which has column S empty and hidden.

In cell R4 I applied this formula:

=LET(d, C4:K14, t, TODAY(), c, d>=t, IF(d = "", "", IF((c) * (d <= t + 30), "?", IF(d, "X", IF(d = "-", "-", IF(NOT(ISNUMBER(d)), NA(), IF((c + 30), UNICHAR(10003), d)))))))

The tick symbol is created by using this function UNICHAR(10003).

Then in CF I used this:

=R4 = "X" (colored in red)

=R4 = "?" (colored in amber)

=R4 = UNICHAR(10003) (colored in green)

=ISNA(R4) (colored in green)

=R4 = "" (colored in green)

The cells in the table A3:K14 that were merged cells I separated into separate cells (see screenshot).

After you have completed the above procedure if you want you can hide columns C:Q.

I hope you do not have a lot of data because the TODAY() function is a volatile function and if you have a lot of data it will affect the computer's performance.

Hope this helps.