color changing cells

[juster21]

i'm working on a spreadsheet app that is used for bill tracking. i need to turn certain cells different colors as the bill due date approaches. any ideas?

[StrangerInBeijing]

As far I know you will need to use a Macro.

If you dont' know how (like me) start recording a Macro, color a cell some color, then go to the Macro Editor and modify the recorded function with some If statement.

I'm sure it's not that difficult.

Good Luck

[sciguyryan]

As far I know you will need to use a Macro.

If you dont' know how (like me) start recording a Macro, color a cell some color, then go to the Macro Editor and modify the recorded function with some If statement.

I'm sure it's not that difficult.

Good Luck

Yes, you would need a Macro, or some VB code in an applocation.

Here is an example:

Range("B4:H4").Select
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 3
    End With

The best way would be for you to experiment using the record macro function

Cheers,

RyanJ

[si_the_geek]

Actually you dont need a macro (although that is an appropriate solution), you can use Conditional Formatting.

Select a cell that you want to be formatted, then on the menu go to "Formatting" - "Conditional Formatting". In the window that appears you can select which format to apply under specific circumstances (based on the cell value, or a formula value).

In this case you need to use a formula, so in the first drop-down select "Formula is", and enter a formula like this:
=INT(TODAY()-B25) >= 3
This will be true if the date in cell B25 is 3 or more days before today.

You can then set the format to apply when the formula is true by clicking on the "Format.." button.


If you want multiple conditional formats for the same cell (ie: one for 3 days, and one for 1 day) you can click the "Add>>" button on the bottom of the Conditional Formatting window.