PDA

View Full Version : Highlighting changed cells



u0107
09-02-2007, 10:44 PM
Hello,

This is my first post here and I would like to describe a problem I have:

Background

1) I have a fairly large single column table (Table_1) which contains valid values and which can be looked up in Vlookup.

2) I have a fairly large area (Area_1) 250 columns x 150 Rows into which values from Table_1 should be permitted.

Problem

1) Considering the large number of entries in Table_1, it is not always guaranteed that valid values will always be entered Area_1

What I want to do:

Whenever a value is entered in Area_1 (say C5:IV155), I would like to validate the entered value and if the value is NOT valid (that is has a matching entry in Table_1), to

a) change the background and / or font colors to something other than the standard area.

b) insert a comment for that cell to explain what is erroneous.

As a counter to the above, when an erroneous entry is corrected, to undo Steps (a) and (b).

Thanks in advance.

Cheers!

Uttam

anandbohra
09-02-2007, 11:51 PM
best way will be to activate track changes option in excel
which gives u undo option like accept or reject changes & keep all the history for the specified period & in proper way as u want.

for activating this
go to Tools-Track Changes - Highlight Changes

u0107
09-02-2007, 11:55 PM
Thanks - but I need the highlighting to be only in a specific tab and in specific range.

This is a code I found somewhere on the net but the limitation is that it does the highlighting in ALL tabs in the same range (C6:IV1005)

What changes do I need to incorporate if I want the highlighting to be restricted to a tab which is named "Assignment Tab"?


-----------------

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Intersect(Target, Range("C6:IV1005")) Is Nothing Then Exit Sub
Target.Interior.ColorIndex = 53
End Sub

mdmackillop
09-03-2007, 12:01 AM
Place the code in particular sheet module and delete it from the workbook module
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C6:IV1005")) Is Nothing Then Exit Sub
Target.Interior.ColorIndex = 53
End Sub

anandbohra
09-03-2007, 12:07 AM
this KB article will help you


http://vbaexpress.com/kb/getarticle.php?kb_id=909

u0107
09-03-2007, 12:07 AM
Hello,

Thank you for a quick response but when I try to delete it from "ThisWorkbook" it gets deleted from the "Assignment Tab" as well.

Call it beginner's dumbness but could you please help further?

Thanks in advance

Uttam

u0107
09-03-2007, 12:24 AM
Thank you Anand for pointing me in the right direction.

I have modified and adapted the code for my use - although I have not yet finished the entire code.

My intent is to provide a rudimentary validation with a user friendly message if the entered value is an error. Valid values are in the lookup table.

Cheers!

Uttam

rbrhodes
09-03-2007, 12:54 AM
Hi U,

Attached example may help as well. It deals with multiple selections etc.

u0107
09-03-2007, 09:55 AM
Hello dr,
:friends:
Fantastic! :bow: This is exactly what I was looking for.

I especially like the clearing of the comments when I erase a value in the cell.:clap2:

I was getting truly worried as to how I was going to clear all those comments which were popping up all over the place when I was testing with incorrect values!:nervous: But your solution gave me a great added bonus!

Thanks again.

Warm regards,

Uttam

dragon576
09-07-2007, 02:16 AM
Hi,
Having used the code to add comments, I now find this has left me with another issue.

I need to be able to tell at a glance if any of the ranges on worksheets have changed in the 5 working days (or week).

Each worksheet I receive has a summary page and then upto 15 sheets containing target ranges with dates.

I initially tried to set a flag on the summary for each sheet by using a value on each sheet, but ended up in a logic loop (in my head), because as soon as a single change was made the flag was set for every week afterwards. This flag was based on a value (1 or 0) and conditional formatting.

Is there a way I can check the comments within a range and see if there has been a change in the last week and then set a flag, and as importent, if no changes reset the flag. This should be triggered by a save event.

Many Thanks

Doug

rbrhodes
09-07-2007, 03:55 AM
Hi Doug,

I'm sorry but I don't understand.

dragon576
09-07-2007, 08:46 AM
Let me try another way. If I use the code in
http://vbaexpress.com/kb/getarticle.php?kb_id=909 to add a comment to a cell if anything is changed in a range of cells, is there any way to identify without looking at each comment that:

a: something has changed in the range of cells
b: if it was changed in a certain time period (1 week)

As there are 15 sheets with the same range (but different values), I need to identify on a summary worksheet which sheet and cell has changed, instead of looking at each comment in each cell for each sheet.

So I am trying to see if I can identify from a comment when it was changed so that I can set a cell on the Summary page to "Red" etc as a visual indicator.

Does that make it clearer?

As I have 7-8 workbooks like this I want to be able to quickly see if anything has changed, otherwise it would take me all day to identify all the changes.

cheers

Doug