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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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 wrote:
>> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > 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 wrote:
>> >> 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" <(E-Mail Removed)> wrote in message
>> >> news:(E-Mail Removed)...
>> >> >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
>> >> >
>> >
>
Similar Threads
