Cell shading based on date conditions

[Bexi]

I want to shade a column based on a date criteria. The date criteria is
entered by the user in column B5 and the day could be other than the
1st.

I have 20 columns that display values and the date is displayed as
heading. For example

Column C Column D Column E
Row 6 7/1/2006 8/1/2006 9/1/2006
Row 7
Row 8 1,000 500
Row 9 500 500
Row 10 500 500
Row 11 500 500

If the user enters 8/31/2006, I want Column D to be shaded. Column B5
changes every month. Is it possible to use Conditional Formatting?

Please help.
Thank you
Bexi

 

[T. Valko]

Try this:

Select the range of cells you want to shade. Based on your posted sample I
guess that would be C6:E11.

Goto Format>Conditional Formatting
Formula Is:

=AND(TEXT($B$5,"mmmyyyy")=TEXT(C$6,"mmmyyyy"),COLUMNS($A:A)=MATCH($B$5,$C$6:$E$6))

Click the Format button
Select the style(s) desired
OK out

You may not need the TEXT functions but I put them in there to "protect"
against any empty cells within your date cells (C6:E6). An empty cell could
cause the MATCH function to return the wrong value thus highlighting the
wrong column.

Biff

 

[Bexi]

It works only for column C. If I change the date on row B5 to 8/20/2006
or 9/25/2006, column D or E is not shaded.

One clarification from my previous email is that the user enters the
date is ROW B5. I want only one of my 20 columns shaded based on the
date entered in row B5. The criteria are 20.

Please help again.

Thank you
Bexi

 

[T. Valko]

It works for me.

Here's a sample file:

cf_columns.xls 14kb

http://cjoint.com/?mlukavlZi8

I suspect that you did not properly apply the conditional formatting. See if
the sample file helps.

Biff

 

[Bexi]

THe cf_columns.xls file is shown as text in the body section of your
email. I cannot open it.

I tested your formula using the sample data I gave you. The range of
cells for shading are C6:E11.

This is how my actual data looks like:

The user enters the date in row C10: (7/20/2006 and the date format is
7-Dec-06)
The rows with the date range are G11:Z11 (7/1/2005,
8/1/2005........12/1/2006 where the date format is Dec-06)
Rows A11:F11 are description headings
The range of cells for shading are G11:Z171. There is a blank row to
separte subtotal and there are a few subtotal rows.

THANKS FOR HELPING.
Bexi

 

[T. Valko]

This works! All you need to do is change the references to reflect your
layout.

Based on your reply the updated (actual) references would be:

=AND(TEXT($C$10,"mmmyyyy")=TEXT(G$11,"mmmyyyy"),COLUMNS($A:A)=MATCH($C$10,$G$11:$Z$11))

The $ signs in the formula are very important. Do not change how they are.
Follow these instructions to the letter!

Select the range to highlight G11:Z171

Select the range STARTING from G11. When you do this properly cell G11 will
be the active cell. All other cells in the selected range will have a
"blueish" color but G11 will not. That means G11 is the active cell. The
references in the formula will automatically increment for all the other
cells. You only need to enter the formula for a single cell.

Goto Format>Conditional Formatting
From the drop down list, select Formula Is.
Enter the above formula in the box on the right.
After the formula is typed into or copied/pasted in that box click the
Format button.
Select whatever format style(s) you want
OK out

Biff

 

[Bexi]

YES, it works. I could not see my error, I placed a $ sign in the text
function (TEXT($G$11.....). Now my worksheet is fully automated.

Thanks so much.
Bexi

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

YES, it works. I could not see my error, I placed a $ sign in the text
function (TEXT($G$11.....). Now my worksheet is fully automated.

Thanks so much.
Bexi