Formatting Text by when last modified
 

Hi,

I have a spreadsheet with a large amount of text values held on it.

In order to improve usability I have been asked to implement a
formatting system whereby any cell which has been modified in the last
2 days is highlighted (e.g. red text).

After the 2 day period, the text is to revert back to blank text
formatting.

Any help, advice or alternative solutions would be much appreciated.

Many thanks.

Richard

Formatting Text by when last modified
 

Use two worksheets.

In the first worksheet include a change event macro that records today's
date in sheet2. For example, if Z100 is changed in sheet1, the macro will
record today's date in sheet2!Z100 and format the text in sheet1!Z100 to red.

The next macro (in a standard module) will scan thru every cell on sheet2
looking for a date. If a date is found, and the date is more than two day's
stale, then the text color of the equivalent cell in sheet1 is changed back
to black. the second macro should be run once a day.
--
Gary's Student


" wrote:

Hi,

I have a spreadsheet with a large amount of text values held on it.

In order to improve usability I have been asked to implement a
formatting system whereby any cell which has been modified in the last
2 days is highlighted (e.g. red text).

After the 2 day period, the text is to revert back to blank text
formatting.

Any help, advice or alternative solutions would be much appreciated.

Many thanks.

Richard

Formatting Text by when last modified
 

Many thanks for your help - I've got the first part working, but I cant
seem to be able to set up the second macro, which searches for =NOW()
and =NOW()-2 -- any ideas?



Gary''s Student wrote:
Use two worksheets.

In the first worksheet include a change event macro that records today's
date in sheet2. For example, if Z100 is changed in sheet1, the macro will
record today's date in sheet2!Z100 and format the text in sheet1!Z100 to red.

The next macro (in a standard module) will scan thru every cell on sheet2
looking for a date. If a date is found, and the date is more than two day's
stale, then the text color of the equivalent cell in sheet1 is changed back
to black. the second macro should be run once a day.
--
Gary's Student


" wrote:

Hi,

I have a spreadsheet with a large amount of text values held on it.

In order to improve usability I have been asked to implement a
formatting system whereby any cell which has been modified in the last
2 days is highlighted (e.g. red text).

After the 2 day period, the text is to revert back to blank text
formatting.

Any help, advice or alternative solutions would be much appreciated.

Many thanks.

Richard

Formatting Text by when last modified
 

Second macro might look like:

Sub gsnu()
Sheets("Sheet2").Activate
For Each r In ActiveSheet.UsedRange
If IsDate(r.Value) Then
If Now() - r.Value 2 Then
s = r.Address
Sheets("Sheet1").Activate
With Range(s).Font
.ColorIndex = xlAutomatic
End With
Sheets("Sheet2").Activate
End If
End If
Next
End Sub

--
Gary''s Student


" wrote:

Hi,

I have a spreadsheet with a large amount of text values held on it.

In order to improve usability I have been asked to implement a
formatting system whereby any cell which has been modified in the last
2 days is highlighted (e.g. red text).

After the 2 day period, the text is to revert back to blank text
formatting.

Any help, advice or alternative solutions would be much appreciated.

Many thanks.

Richard