Conditional Formatting

[Gladston]

Hello Excel Geniuses!

Hope all is well.

I am in need of some assistance, and I am hoping someone can help me here...

I have a workbook where months, expressed as the 1st of the month, are in row 2. Each column to the right is the subsequent month. SO for example, 10/1/2015 in BW2, 11/1/2015 in BX2, and so on...

I have some numbers, that are formulas, far below the previously mentioned dates. These numbers rage from rows 110 to 114. So, for example, I have:

Amount - Cell
$1 - BW110
$10 - BW111
$100 - BW112
$1000 - BW113
$10000 - BW114

Similarly, in each column to the right, there is a new set of numbers that relates to the month in Row 2.

Here's my question... I would like to have the cells between rows 110 through 114 in any given column be highlighted when the date in row 2 (within the same column) equals a date within the previous month of the current date. So for example...

If I opened this workbook up at any point within the month of NOVEMBER 2015, I would like the cells in BW110 through BW114 to be highlighted, all because BW2 = 10/1/2015 (aka the date falling within the previous month). So the same logic would be true if BW2 = 10/15/2015, or 10/31/2015, or 10/7/2015... As long as the date is within the previous month, cells 110-114 within the same column should highlighted.

Following the same logic, I also have another row (row 4) that I would like to be formatted the same way. So, if I open the workbook at any point in November 2015, I want BW4 to also be highlighted since BW2 is a date within the past month (October 2015).

I would like this formatting to be able to be copied over so each month the highlighting updates on its own.

Hope this makes sense, and I hope someone can help me with the equation for conditional formatting.

Your help would be GREATLY appreciated.

Thanks in advance!

Brad

 

[etaf]

try this

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

Set-up a conditional formatting

Select the range
BW110:BZ114
and then using the control Key
also select BW4:BZ4
Or what range you want to highlight

in the range now you should see
=$BW$4:$BZ$4,$BW$10:$BZ$14

now using conditional formatting
=MONTH(BW$2)=MONTH(TODAY())-1

and choose a colour

to make that work across years - you should use
=MONTH(B2)=MONTH(EOMONTH(F2,-1))

so that when the date is
1st Jan 2016 - to 31/1/16
December will highlight

 

[ArthriticPanda]

Hi Gladston,

You could use the following formula in the conditional format using the formula option:

=MONTH($A$1)=MONTH($G$1)-1

Excel Workbook
	A	B	C	D	E	F	G
1	01/09/2015	01/10/2015	01/11/2015			Today:	18/10/2015
2
3	$ 1	$ 1	$ 1
4	$ 10	$ 10	$ 10
5	$ 100	$ 100	$ 100
6	$ 1,000	$ 1,000	$ 1,000
7	$ 10,000	$ 10,000	$ 10,000
Sheet1

Regards,

AP

 

[Gladston]

Thanks everyone! Your suggestions worked perfectly! Much appreciated!