Hello all,

I have a formating issue, and I am not sure the best way to go about it. I
have a spreadsheet that shows each day in a different column. I want any
projections (items in columns for days past the current or past days) to have
the background highlighted yellow. I know how to use the conditional format
in Excel, but there are so many cells, I have to adjust each one
individually. Is there a way maybe through Visual Basic that I can use to
"mass format" a specific area?

Here is what I have so far:

row 1 = column date headers ( B1 = 3/1/05, C1 = 3/2/05, D1 = 3/3/05, etc)

conditional formatting:

B2 =IF(B2="","",AND(B20,TODAY()<B1))
B3 =IF(B3="","",AND(B30,TODAY()<B1))
Etc down the row and across all columns.

Any help is appreciated!
BrentM

Brent

I suspect you were very close to the cure yourself. Try:

1) Highlight the area you wish to conditionally format
2) Format, conditional format, Formula Is
3) Enter =IF(B2="","",AND(B20,TODAY()<B$1))

If this doesn't work, study the conditional format formula in one of the
cells that is not giving the correct result. Take a close look at which row
and column you need to change to fixed reference to cure the problem.

HTH
Steve

"brentm" wrote in message
...
Hello all,

I have a formating issue, and I am not sure the best way to go about it.
I
have a spreadsheet that shows each day in a different column. I want any
projections (items in columns for days past the current or past days) to
have
the background highlighted yellow. I know how to use the conditional
format
in Excel, but there are so many cells, I have to adjust each one
individually. Is there a way maybe through Visual Basic that I can use to
"mass format" a specific area?

Here is what I have so far:

row 1 = column date headers ( B1 = 3/1/05, C1 = 3/2/05, D1 = 3/3/05, etc)

conditional formatting:

B2 =IF(B2="","",AND(B20,TODAY()<B1))
B3 =IF(B3="","",AND(B30,TODAY()<B1))
Etc down the row and across all columns.

Any help is appreciated!
BrentM

Steve,

thanks for the help, however, it isn't what I need. I probably didn't get
my true problem across clearly. Here is a mock sample:

K L M N
6 3/9/05 3/10/05 3/11/05 3/12/05
40 1000.00 450.00 375.00
41 500.00 825.00 1125.00
42 550.00 750.00 725.00

Since today is 3/9/05, I need the cells in columns L-N to be highlighted if
there is a value in that cell. Cells L41 & 42, M40, N40-42 should be
highlighted since they are in a future period. Does that make more sense?
Currently, I have my formula, which works great, but it has to be copied into
each cell's formating formula, then modified to correct the proper cell
references.

Any ideas?

BrentM



"Steve R" wrote:

Brent

I suspect you were very close to the cure yourself. Try:

1) Highlight the area you wish to conditionally format
2) Format, conditional format, Formula Is
3) Enter =IF(B2="","",AND(B20,TODAY()<B$1))

If this doesn't work, study the conditional format formula in one of the
cells that is not giving the correct result. Take a close look at which row
and column you need to change to fixed reference to cure the problem.

HTH
Steve

"brentm" wrote in message
...
Hello all,

I have a formating issue, and I am not sure the best way to go about it.
I
have a spreadsheet that shows each day in a different column. I want any
projections (items in columns for days past the current or past days) to
have
the background highlighted yellow. I know how to use the conditional
format
in Excel, but there are so many cells, I have to adjust each one
individually. Is there a way maybe through Visual Basic that I can use to
"mass format" a specific area?

Here is what I have so far:

row 1 = column date headers ( B1 = 3/1/05, C1 = 3/2/05, D1 = 3/3/05, etc)

conditional formatting:

B2 =IF(B2="","",AND(B20,TODAY()<B1))
B3 =IF(B3="","",AND(B30,TODAY()<B1))
Etc down the row and across all columns.

Any help is appreciated!
BrentM

Brent


If I understand you correctly, values to be highlighted in M, N & O are
dependant on the date in L6. Because your dates increase by a day, across
the columns, the following should work:

a) Enter this conditional format formula at K7:
=AND(NOT(ISBLANK(L7)),L$6TODAY())

2) Use format painter or copy,.paste special, formats to format other cells.

BTW In my neck of the woods, today is actually 10/3/2005 but, let's not get
into debate <bg
Steve


"brentm" wrote in message
...
Steve,

thanks for the help, however, it isn't what I need. I probably didn't get
my true problem across clearly. Here is a mock sample:

K L M N
6 3/9/05 3/10/05 3/11/05 3/12/05
40 1000.00 450.00 375.00
41 500.00 825.00 1125.00
42 550.00 750.00 725.00

Since today is 3/9/05, I need the cells in columns L-N to be highlighted
if
there is a value in that cell. Cells L41 & 42, M40, N40-42 should be
highlighted since they are in a future period. Does that make more sense?
Currently, I have my formula, which works great, but it has to be copied
into
each cell's formating formula, then modified to correct the proper cell
references.

Any ideas?

BrentM



"Steve R" wrote:

Brent

I suspect you were very close to the cure yourself. Try:

1) Highlight the area you wish to conditionally format
2) Format, conditional format, Formula Is
3) Enter =IF(B2="","",AND(B20,TODAY()<B$1))

If this doesn't work, study the conditional format formula in one of the
cells that is not giving the correct result. Take a close look at which
row
and column you need to change to fixed reference to cure the problem.

HTH
Steve

"brentm" wrote in message
...
Hello all,

I have a formating issue, and I am not sure the best way to go about
it.
I
have a spreadsheet that shows each day in a different column. I want
any
projections (items in columns for days past the current or past days)
to
have
the background highlighted yellow. I know how to use the conditional
format
in Excel, but there are so many cells, I have to adjust each one
individually. Is there a way maybe through Visual Basic that I can use
to
"mass format" a specific area?

Here is what I have so far:

row 1 = column date headers ( B1 = 3/1/05, C1 = 3/2/05, D1 = 3/3/05,
etc)

conditional formatting:

B2 =IF(B2="","",AND(B20,TODAY()<B1))
B3 =IF(B3="","",AND(B30,TODAY()<B1))
Etc down the row and across all columns.

Any help is appreciated!
BrentM

Steve,

Thanks a million! SO what part of the world do you hail from?

Brent

"Steve R" wrote:

Brent


If I understand you correctly, values to be highlighted in M, N & O are
dependant on the date in L6. Because your dates increase by a day, across
the columns, the following should work:

a) Enter this conditional format formula at K7:
=AND(NOT(ISBLANK(L7)),L$6TODAY())

2) Use format painter or copy,.paste special, formats to format other cells.

BTW In my neck of the woods, today is actually 10/3/2005 but, let's not get
into debate <bg
Steve


"brentm" wrote in message
...
Steve,

thanks for the help, however, it isn't what I need. I probably didn't get
my true problem across clearly. Here is a mock sample:

K L M N
6 3/9/05 3/10/05 3/11/05 3/12/05
40 1000.00 450.00 375.00
41 500.00 825.00 1125.00
42 550.00 750.00 725.00

Since today is 3/9/05, I need the cells in columns L-N to be highlighted
if
there is a value in that cell. Cells L41 & 42, M40, N40-42 should be
highlighted since they are in a future period. Does that make more sense?
Currently, I have my formula, which works great, but it has to be copied
into
each cell's formating formula, then modified to correct the proper cell
references.

Any ideas?

BrentM



"Steve R" wrote:

Brent

I suspect you were very close to the cure yourself. Try:

1) Highlight the area you wish to conditionally format
2) Format, conditional format, Formula Is
3) Enter =IF(B2="","",AND(B20,TODAY()<B$1))

If this doesn't work, study the conditional format formula in one of the
cells that is not giving the correct result. Take a close look at which
row
and column you need to change to fixed reference to cure the problem.

HTH
Steve

"brentm" wrote in message
...
Hello all,

I have a formating issue, and I am not sure the best way to go about
it.
I
have a spreadsheet that shows each day in a different column. I want
any
projections (items in columns for days past the current or past days)
to
have
the background highlighted yellow. I know how to use the conditional
format
in Excel, but there are so many cells, I have to adjust each one
individually. Is there a way maybe through Visual Basic that I can use
to
"mass format" a specific area?

Here is what I have so far:

row 1 = column date headers ( B1 = 3/1/05, C1 = 3/2/05, D1 = 3/3/05,
etc)

conditional formatting:

B2 =IF(B2="","",AND(B20,TODAY()<B1))
B3 =IF(B3="","",AND(B30,TODAY()<B1))
Etc down the row and across all columns.

Any help is appreciated!
BrentM

I'm an Aussie, currently living in the far north of Sydney.


"brentm" wrote in message
...
Steve,

Thanks a million! SO what part of the world do you hail from?

Brent

"Steve R" wrote:

Brent


If I understand you correctly, values to be highlighted in M, N & O are
dependant on the date in L6. Because your dates increase by a day, across
the columns, the following should work:

a) Enter this conditional format formula at K7:
=AND(NOT(ISBLANK(L7)),L$6TODAY())

2) Use format painter or copy,.paste special, formats to format other
cells.

BTW In my neck of the woods, today is actually 10/3/2005 but, let's not
get
into debate <bg
Steve


"brentm" wrote in message
...
Steve,

thanks for the help, however, it isn't what I need. I probably didn't
get
my true problem across clearly. Here is a mock sample:

K L M
N
6 3/9/05 3/10/05 3/11/05 3/12/05
40 1000.00 450.00 375.00
41 500.00 825.00
1125.00
42 550.00 750.00 725.00

Since today is 3/9/05, I need the cells in columns L-N to be
highlighted
if
there is a value in that cell. Cells L41 & 42, M40, N40-42 should be
highlighted since they are in a future period. Does that make more
sense?
Currently, I have my formula, which works great, but it has to be
copied
into
each cell's formating formula, then modified to correct the proper cell
references.

Any ideas?

BrentM



"Steve R" wrote:

Brent

I suspect you were very close to the cure yourself. Try:

1) Highlight the area you wish to conditionally format
2) Format, conditional format, Formula Is
3) Enter =IF(B2="","",AND(B20,TODAY()<B$1))

If this doesn't work, study the conditional format formula in one of
the
cells that is not giving the correct result. Take a close look at
which
row
and column you need to change to fixed reference to cure the problem.

HTH
Steve

"brentm" wrote in message
...
Hello all,

I have a formating issue, and I am not sure the best way to go about
it.
I
have a spreadsheet that shows each day in a different column. I
want
any
projections (items in columns for days past the current or past
days)
to
have
the background highlighted yellow. I know how to use the
conditional
format
in Excel, but there are so many cells, I have to adjust each one
individually. Is there a way maybe through Visual Basic that I can
use
to
"mass format" a specific area?

Here is what I have so far:

row 1 = column date headers ( B1 = 3/1/05, C1 = 3/2/05, D1 = 3/3/05,
etc)

conditional formatting:

B2 =IF(B2="","",AND(B20,TODAY()<B1))
B3 =IF(B3="","",AND(B30,TODAY()<B1))
Etc down the row and across all columns.

Any help is appreciated!
BrentM

Steve,

Thanks for your help. It still isn't working as I had hoped, so I have just
manually altered the formatting for each cell. Time consuming, but at least
it is finished and I can move on to something new.

Brent

"Steve R" wrote:

I'm an Aussie, currently living in the far north of Sydney.


"brentm" wrote in message
...
Steve,

Thanks a million! SO what part of the world do you hail from?

Brent

"Steve R" wrote:

Brent


If I understand you correctly, values to be highlighted in M, N & O are
dependant on the date in L6. Because your dates increase by a day, across
the columns, the following should work:

a) Enter this conditional format formula at K7:
=AND(NOT(ISBLANK(L7)),L$6TODAY())

2) Use format painter or copy,.paste special, formats to format other
cells.

BTW In my neck of the woods, today is actually 10/3/2005 but, let's not
get
into debate <bg
Steve


"brentm" wrote in message
...
Steve,

thanks for the help, however, it isn't what I need. I probably didn't
get
my true problem across clearly. Here is a mock sample:

K L M
N
6 3/9/05 3/10/05 3/11/05 3/12/05
40 1000.00 450.00 375.00
41 500.00 825.00
1125.00
42 550.00 750.00 725.00

Since today is 3/9/05, I need the cells in columns L-N to be
highlighted
if
there is a value in that cell. Cells L41 & 42, M40, N40-42 should be
highlighted since they are in a future period. Does that make more
sense?
Currently, I have my formula, which works great, but it has to be
copied
into
each cell's formating formula, then modified to correct the proper cell
references.

Any ideas?

BrentM



"Steve R" wrote:

Brent

I suspect you were very close to the cure yourself. Try:

1) Highlight the area you wish to conditionally format
2) Format, conditional format, Formula Is
3) Enter =IF(B2="","",AND(B20,TODAY()<B$1))

If this doesn't work, study the conditional format formula in one of
the
cells that is not giving the correct result. Take a close look at
which
row
and column you need to change to fixed reference to cure the problem.

HTH
Steve

"brentm" wrote in message
...
Hello all,

I have a formating issue, and I am not sure the best way to go about
it.
I
have a spreadsheet that shows each day in a different column. I
want
any
projections (items in columns for days past the current or past
days)
to
have
the background highlighted yellow. I know how to use the
conditional
format
in Excel, but there are so many cells, I have to adjust each one
individually. Is there a way maybe through Visual Basic that I can
use
to
"mass format" a specific area?

Here is what I have so far:

row 1 = column date headers ( B1 = 3/1/05, C1 = 3/2/05, D1 = 3/3/05,
etc)

conditional formatting:

B2 =IF(B2="","",AND(B20,TODAY()<B1))
B3 =IF(B3="","",AND(B30,TODAY()<B1))
Etc down the row and across all columns.

Any help is appreciated!
BrentM

Brent

If it worked yesterday and did not work today, my guess is that you need to
recalculate. Pressing F9 will cause you workbook to recalculate but, you can
automate recalcuation of just the relevant sheet with a tiny piece of code:

a) Right click on the sheet tab with the formatting
b) Click View Code
c) Copy and paste the following:

Private Sub Worksheet_Activate()
ActiveSheet.Calculate
End Sub

Note, if you try to write the code, there is a dot between Active and Sheet.

Alternatively, force the whole workbook to recalculate on open:
a) Press Alt/F11
b) Double click ThisWorkbook
c) Copy and paste the following

Private Sub Workbook_Open()
Calculate
End Sub


HTH
Steve


"brentm" wrote in message
...
Steve,

Thanks for your help. It still isn't working as I had hoped, so I have
just
manually altered the formatting for each cell. Time consuming, but at
least
it is finished and I can move on to something new.

Brent

"Steve R" wrote:

I'm an Aussie, currently living in the far north of Sydney.


"brentm" wrote in message
...
Steve,

Thanks a million! SO what part of the world do you hail from?

Brent

"Steve R" wrote:

Brent


If I understand you correctly, values to be highlighted in M, N & O
are
dependant on the date in L6. Because your dates increase by a day,
across
the columns, the following should work:

a) Enter this conditional format formula at K7:
=AND(NOT(ISBLANK(L7)),L$6TODAY())

2) Use format painter or copy,.paste special, formats to format other
cells.

BTW In my neck of the woods, today is actually 10/3/2005 but, let's
not
get
into debate <bg
Steve


"brentm" wrote in message
...
Steve,

thanks for the help, however, it isn't what I need. I probably
didn't
get
my true problem across clearly. Here is a mock sample:

K L M
N
6 3/9/05 3/10/05 3/11/05 3/12/05
40 1000.00 450.00
375.00
41 500.00 825.00
1125.00
42 550.00 750.00
725.00

Since today is 3/9/05, I need the cells in columns L-N to be
highlighted
if
there is a value in that cell. Cells L41 & 42, M40, N40-42 should
be
highlighted since they are in a future period. Does that make more
sense?
Currently, I have my formula, which works great, but it has to be
copied
into
each cell's formating formula, then modified to correct the proper
cell
references.

Any ideas?

BrentM



"Steve R" wrote:

Brent

I suspect you were very close to the cure yourself. Try:

1) Highlight the area you wish to conditionally format
2) Format, conditional format, Formula Is
3) Enter =IF(B2="","",AND(B20,TODAY()<B$1))

If this doesn't work, study the conditional format formula in one
of
the
cells that is not giving the correct result. Take a close look at
which
row
and column you need to change to fixed reference to cure the
problem.

HTH
Steve

"brentm" wrote in message
...
Hello all,

I have a formating issue, and I am not sure the best way to go
about
it.
I
have a spreadsheet that shows each day in a different column. I
want
any
projections (items in columns for days past the current or past
days)
to
have
the background highlighted yellow. I know how to use the
conditional
format
in Excel, but there are so many cells, I have to adjust each one
individually. Is there a way maybe through Visual Basic that I
can
use
to
"mass format" a specific area?

Here is what I have so far:

row 1 = column date headers ( B1 = 3/1/05, C1 = 3/2/05, D1 =
3/3/05,
etc)

conditional formatting:

B2 =IF(B2="","",AND(B20,TODAY()<B1))
B3 =IF(B3="","",AND(B30,TODAY()<B1))
Etc down the row and across all columns.

Any help is appreciated!
BrentM