Data Validation Help

[Guest]

Hello all experts,

I have a worksheet which contains the data, and I would like to set up a
data validation function that whenever a user updates a cell, the cell will
change the color from white to red.

How can i do it? Please help.

Thanks million.

 

[Guest]

Perhaps you mean this kind of play using conditional formatting ..

Assume inputs will be made in A1 down

Select col A (click on the col header "A")
Click Format > Conditional Formatting
Under condition 1, make it as:
Formula is: =A1<>""
Click Format button > Red fill n white font/bolded? > OK
Click OK at the main dialog

Test it out .. When inputs are made in col A, the cells will be formatted:
Red fill n white font/bolded. When you clear the cells (ie press Delete),
it'll turn back to "white"

 

[Guest]

Hello,

Thank you. However, all my cells are already filled with information, ie.
all cells are non blank. So it does not work.

What else can I do? All my cells are non blank, but I would like to know
which cells have been changed by users.

Thanks

 

[Guest]

.. all cells are non blank.

Ah, sorry to have missed that earlier. It's considerably more difficult now,
but to an extent, perhaps you could try this op/play ..

Assuming source data is in col A in sheet: X

Make a copy of X for the daily updates, name this sheet as say: Y

In Y,
Select col A (click on the col header "A")
Click Format > Conditional Formatting
Under condition 1, make it as:
Formula is: =A1<>INDIRECT("'X'!A"&ROW(A1))
Click Format button > Red fill n white font/bolded? > OK
Click OK at the main dialog

When "updates" are made in col A, ie it's presumed the data values are
changed to other/different values, then the cells will be formatted: Red fill
n white font/bolded. If the update involves clearing data in Y, the format
will also be triggered.

Then at the end of each day, after you've noted whatever's triggered in Y,
simply copy col A in Y and overwrite col A in X with a paste special as
values. This readies Y for the next day's updates. In daily operation, X
would/could be hidden safely away.

 

[Guest]

hello max,
hi kaci, i guess you're also an expert cause you share your files to other
users..
Did you try Tools>Track Changes
Online Help files will give you the control to even accept or reject changes
made by other users...
I guess this is the reason why you like to validate changes.
the color conditional format with Max suggestion is the best way.
happy holidays.

 

[Guest]

Take a look in Tools > Track changes

 

[Guest]

Hello Max,

I tried your method by using exactly X and Y worksheet, then using Col A,
but it still does not work. Whenever I made changes in X, the Y worksheet
does not show the conditional formating.

Please kindly advise.

 

[Max]

.. Whenever I made changes in X, ...

You got it wrong. The updates are supposed to be made in Y, not X. X is the
base reference. Pl read the earlier suggestion carefully. It should work as
stated.

---