MACRO / VBA HELP (auto highlight)

[buddygunner]

I require a Macro that will auto highlight new entries made to an excel sheet for a set time. can any body help on this?

 

[RickXL]

Hi and welcome to the MrExcel Message Board,

I would do this by adding a creation time into a spare column somewhere. You could use either a date or a date/time variable. I used date/time in the example below.

Then you need some VBA that will respond to a Worksheet Change event. The code will check to see if a date/time value has been entered already or not. If not then it will add one.

Finally, you can use Conditional Formatting to highlight the rows depending on how close the creation date/time is to the current date/time.

Private Sub Worksheet_Change(ByVal Target As Range)
    Const ColumnForDate As String = "C"
    Dim r As Range
    If Target.Count > 1 Then Exit Sub
    Set r = Intersect(Target.EntireRow, Columns(ColumnForDate))
    If Not r Is Nothing Then
        If r.Value = vbNullString Then r.Value = Now
    End If
End Sub

The code needs to be pasted in as code for the sheet where you want all this to happen.
Note: the Const ColumnForDate As String = "C" line is set to use column C for the date/time value/ Please change that to a suitable letter for your purposes.

The Conditional Formatting equation I used was:
=$C1>NOW()-(1/8640)
That removes the highlighting after 10 seconds.
The column used in that equation needs to match the one used by the macro.

 

[buddygunner]

Thank you this worked just how i had hoped apart for 1 little bit : -

The Conditional Formatting equation I used was:
=$C1>NOW()-(1/8640)
That removes the highlighting after 10 seconds.

what figure would i have to use to remove the highlighting after 7 days

Cheers again

 

[buddygunner]

An on another note,

I also require a macro that will enter the current date & time into a cell when that cell is clicked. if at all possible

 

[RickXL]

You could try:

=$C1>(NOW()-7)

 

[RickXL]

An on another note,

I also require a macro that will enter the current date & time into a cell when that cell is clicked. if at all possible

Excel can be made to enter a value when one of these things happens:

1. When the cell is Selected;
2. When a cell is Double-Clicked
3. When a cell is Right-Clicked.

You also need to decide what happens when the action is repeated. Do you want the date/time to be replaced of left alone?

There are shortcut keys that will enter dates as well: Excel 2002: Use This Shortcut to Insert Time/Date in Excel or Access

 

[buddygunner]

I work with people who are lets say not use to technology, so i'm trying to make a spread sheet that they are able to use with the minimum of fuss.


I would like it to happen when the Cell is selected, then it to be left alone after that.



Thanks for your help so far, i am a complete biginner when it comes to VBA so your help is greatly appreciated.

 

[RickXL]

Before I proceed, did the code above work?

Does it write the current date/time into the cell in column C automatically when you make a change to a row?

If you want the code to work for Sheet1 then you need to paste the code into the Module for Sheet1.

 

[buddygunner]

LOL
sorry to mess you about.
now that i've added the code module in to the correct place it is working how i wanted it to so no need for any extra code.
I had copied it as a module for the work book rather than each seperate sheet.

thankyou very much for your help.

 

[RickXL]

No problem at all. It was my fault for expecting you to know.

Basically, you should always put general code into a standard Module.
If Events are involved then you would use a Sheet Module.

I did think that I perhaps ought to explain how it works as well. However, when I looked at the code in detail I noticed that it could be improved and simplified so here is the new version:

Private Sub Worksheet_Change(ByVal Target As Range)
    Const ColumnForDate As String = "C"
    If Target.Count > 1 Then Exit Sub
    Application.EnableEvents = False
    If Target(1, ColumnForDate) = vbNullString Then Target(1, ColumnForDate).Value = Now
    Application.EnableEvents = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

That code is standard for a worksheet change event. It is supplied by Excel and is seldom, if ever, modified.
The value called "Target" is the range that was changed.

    Const ColumnForDate As String = "C"

That is where you select the column you want to put the date/time value into. Just replace the "C" with another letter to choose another column.

    If Target.Count > 1 Then Exit Sub

That line looks at the Target range and counts the number of cells in the range. If the user has changed more than one cell then the sub exited and no more code runs.

    Application.EnableEvents = False

When the macro adds the date/time value it changes the worksheet. That re-triggers the same macro. This line stops that happening. The macro now only runs once per worksheet change.

    If Target(1, ColumnForDate) = vbNullString Then Target(1, ColumnForDate).Value = Now

This is the line that looks in the chosen cell and puts a date/time value in if it is blank i.e. contains a null string.