Conditional Formatting on Excel 2010

Closed
Clue J - Dec 2, 2014 at 11:31 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Dec 4, 2014 at 11:34 AM
Hi,

I am trying to figure out the correct method in which to apply a formula in conditional formatting. The first row contain the document names. The coulmns row contain due dates in a weekly calendar format. I want to highlight the document cell(in the first row) according to the due dates, if they fall within due date, one week, or one month in the specific row of the document cell. Ex.
Week
Document Jan 5 Jan 12 Jan 19 Jan 26

A Jan 15
B Jan24
C Jan6

What i would like to do is have the formula go through the row for Document A and see if there is a date matching today's date. If there is then I would like to Higlight cell that contains A.

The current formula I am using in conditional formatting for the first document A cell is:

=$E$3:$G$3=$B$27 [if true it will higlight the specific document cell red, B27 is today()]
I feel this formula should go through the dates in that row and if it finds a date that matches it will highlit document A cell.

Currently it does not work. It works if I change the above formula to specify each cell in the row but that would mean creating 100 formula's. Is there another way.

Thanks
Related:

1 response

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Dec 4, 2014 at 11:34 AM
Hi Clue J,

An array formula would be nice to use in this situation, but unfortunately they don't work with conditional format.

What you can do is use an array formula in an empty column and then refer to the outcome of that formula in your conditional format formula.

Use this array formula in an empty column (out of sight, on different sheet, hide column):
=SUM(IF($E3:$G3=$B$27,1,0))
Array formula's need to be confirmed by hitting Ctrl+Shift+Enter. If done correctly the formula will be enclosed by curly brackets.

The formula basically counts the number of times the value in B27 is present in the given range. So in conditional format you can say that if the value is bigger then 0, then color the cell.

I hope this is clear, let me know if it's not.

Best regards,
Trowa
0