Conditional Formatting

[Guest]

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(B2>0,TODAY()<B1))
B3 =IF(B3="","",AND(B3>0,TODAY()<B1))
Etc down the row and across all columns.

Any help is appreciated!
BrentM

 

[Steve R]

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(B2>0,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

 

[Guest]

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]

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$6>TODAY())

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

 

[Guest]

Steve,

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

Brent

 

[Steve R]

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

 

[Guest]

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]

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