Conditional Formatting on Excel 2010

[Closed]
Report
-
Posts
2817
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 14, 2021
-
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

1 reply

Posts
2817
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 14, 2021
486
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