I have conditional formatting set up on a column of data so that the cell at
the top of the column (A1) is white. All the other cells below are yellow.
This is their normal formatting status.

A user puts some information in cell A1. Conditional formatting in cell A2
(formula is =A1<"") now changes cell A2 to white. This acts as a kind of a
prompt, showing the user where to put the next bit of data. And when the
user puts the data in A2, then A3 goes white (using =A2<"") and so on. It
works nicely. Same thing all the way down that column.

I can clear those items out using the delete key, or using a bit of code
like range("a1:a5").clearcontents. It works fine and they all turn back to
yellow.

BUT
If I go into a different workbook, and type 3 or 4 data items in a column,
then I copy those back into my sheet, and paste them using pastespecial
values, it all seems to have worked well. The cells are white, and the next
cell below is also white. However, now if I press the delete key, or use my
clearcontents program, they don't change back to yellow. Excel thinks that
the cells are not empty any more, even though I thought I was only pasting
values in there.

What is going on?

--
Allllen

When you "cut and paste" copy you are taking the formatting from the
originating cells and putting it into the cells that you paste the
information into. If, for example, the pasted cells were red, your yellow
cells would be red after the paste operation.

If you want just the values (which is what you seem to want), you will need
to "Paste Special..." and choose "Values".

"Allllen" wrote:

I have conditional formatting set up on a column of data so that the cell at
the top of the column (A1) is white. All the other cells below are yellow.
This is their normal formatting status.

A user puts some information in cell A1. Conditional formatting in cell A2
(formula is =A1<"") now changes cell A2 to white. This acts as a kind of a
prompt, showing the user where to put the next bit of data. And when the
user puts the data in A2, then A3 goes white (using =A2<"") and so on. It
works nicely. Same thing all the way down that column.

I can clear those items out using the delete key, or using a bit of code
like range("a1:a5").clearcontents. It works fine and they all turn back to
yellow.

BUT
If I go into a different workbook, and type 3 or 4 data items in a column,
then I copy those back into my sheet, and paste them using pastespecial
values, it all seems to have worked well. The cells are white, and the next
cell below is also white. However, now if I press the delete key, or use my
clearcontents program, they don't change back to yellow. Excel thinks that
the cells are not empty any more, even though I thought I was only pasting
values in there.

What is going on?

--
Allllen

As I mentioned, I am already pasting using paste special values.
I am deliberately trying not to paste the formatting.

--
Allllen


"chemist" wrote:

When you "cut and paste" copy you are taking the formatting from the
originating cells and putting it into the cells that you paste the
information into. If, for example, the pasted cells were red, your yellow
cells would be red after the paste operation.

If you want just the values (which is what you seem to want), you will need
to "Paste Special..." and choose "Values".

"Allllen" wrote:

I have conditional formatting set up on a column of data so that the cell at
the top of the column (A1) is white. All the other cells below are yellow.
This is their normal formatting status.

A user puts some information in cell A1. Conditional formatting in cell A2
(formula is =A1<"") now changes cell A2 to white. This acts as a kind of a
prompt, showing the user where to put the next bit of data. And when the
user puts the data in A2, then A3 goes white (using =A2<"") and so on. It
works nicely. Same thing all the way down that column.

I can clear those items out using the delete key, or using a bit of code
like range("a1:a5").clearcontents. It works fine and they all turn back to
yellow.

BUT
If I go into a different workbook, and type 3 or 4 data items in a column,
then I copy those back into my sheet, and paste them using pastespecial
values, it all seems to have worked well. The cells are white, and the next
cell below is also white. However, now if I press the delete key, or use my
clearcontents program, they don't change back to yellow. Excel thinks that
the cells are not empty any more, even though I thought I was only pasting
values in there.

What is going on?

--
Allllen