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.

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"
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"kaci" wrote:
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.

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

"Max" wrote:

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"
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"kaci" wrote:
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.

.. 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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"kaci" wrote:
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

hello max,
hi kaci, i guess you're also an expert cause you share your files to other
users..
Did you try ToolsTrack 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.

"kaci" wrote:

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

"Max" wrote:

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"
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"kaci" wrote:
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.

Take a look in Tools Track changes


"kaci" wrote:

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.

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" wrote:

.. 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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"kaci" wrote:
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

.. 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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"kaci" wrote in message
...
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" wrote:

.. 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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"kaci" wrote:
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