Conditional Formatting

[stansen]

Hi guys!

I need help with conditional formatting...
I have 12 columns with 5 rows each. The columns headings are "dates" (date format mm/yyyy). Within each column, there are values (e.g. sales for 6 companies in the given month - some of them can be sums, formulas or refer to other parts of excel sheet/s/ ....).
It looks like this:

----------------------------08/2011 09/2011 10/2011 ....

Company A-----------$10,000 $14,000 $9,000
Company B-----------$12,000 $12,000 $7,000
Company C-----------$11,000 $12,000 $10,000
Company D-----------$15,000 $14,000 $11,000
Company E-----------$16,000 $12,000 $10,000
Company F-----------$18,000 $14,000 $20,000

I would like to achieve that the column with the real (actual) month be highlighted with all its values below... (So, since today it is 28 September 2011, the column 09/2011 with all its values below should be highlighted e.g. in red..... in 3 days on 1 October, another column should be automatically highlighted..

What I can achieve now is that only the cell with the actual "month" is highlighted... (today it is the cell "09/2011") (Conditional Formatting - This Month, -in Excel 2010.). But I do not know how to get the values beneath the actual month's cell highlighted as well...
I'd need a condition based on format, something like this: "If the cell A2 is highlighted, highlight the cells B2:G2 as well"... (I would need to solve the issue in Excel 2010 but also in older versions as well...)
Any ideas?
Thanks
stan

[NBVC]

What formula are you using to highlight currently?

Use that same formula and "freeze" the row reference in the formula with a $ preceding the row number, like A$2 and then apply the conditional format to the whole range.

[stansen]

Hi,
I know what you mean, but it does not work so easily..
I have applied the automatic option in Excel 2010: Conditional Formatting - Highlight Cells Rules - Is a date that occurs in a particular range (look at this http://www.excel-2010.com/conditiona...ft-excel-2010/)

I haven't devised any formula myself... I have only selected a predefined option in Excel 2010... (so I do not even see what's 'behind the highlighted cell' to be able to 'freeze' something or extend the formula to other cells....) Moreover, the column headings are in a date format whereas the cells below are numbers...

[NBVC]

Ok,

Select the whole range of data with date headers, including the headers.

Re-invoke Conditional Formatting from the Home tab, and select Manage Rules

If there are any conditional formats there, delete them.

Then select Use a formula to determine which cells to format from top section and enter formula:

=MONTH(B$1)=MONTH(TODAY())

where B1 is the top left most cell containing a date header in your selection. Change if necessary.

Click Format and choose colour.

[stansen]

Thank you! Amazing!... But I still wonder how does this formula work... only the cell with the month (which has been properly identified with the formula, i.e. the "September cell" should be marked rather than the whole column....since there is no "range" present in your formula according to which a range of cells beneath the actual date should be highlighted....there is only a range within which the excel should be looking for a 'proper date cell'..) But still it works as it should..
Have a nice day
stan

[NBVC]

The $ in B$1 "freezes" that reference. So the conditional format is looking always as row 1. There is no $ before the B, so the column reference is not "frozen", it is relative to the position you are in.

Each cell in the selection looks at the Row 1 reference and in the column the cell is in, if the date in that reference is the same as today's date (month only), then each cell is highlighted since you selected and applied the same format to each cell.

Conditional formatting is a bit confusing in that respect... it takes a bit of getting used to.

Have a look here at an intro to Conditional Formatting and then specifically at Formatting Based on Another Cell