macro to ensure conditional formatting
 

I've got a column where the entries are checked against all fields of
column on another worksheet. If there's no match the entry is invali
and colored red. This is accomplished by means of Conditiona
Formatting:


Code
-------------------
=EN(AANTAL.ALS(Referentie_Lijst;C5)=0;NIET(ISLEEG( C5))
-------------------


I've also written a macro which does the checking, but for ALL entr
fields of that column; not just the one which was just entered. Th
reason for this was that I did some testing with the sheet. At som
point I had so many test values that I simply selected them all an
pressed [del]. To my surprise the Conditional Formatting for the field
of that particular column was deleted as well.

Is there a real problem as I got a macro which takes care of things a
the end by checking them all by means of a button? Well, the people wh
will be using the sheet for entry wish for the immediate check pe
entry.

Is there a way by means of a macro or so to ensure that the Conditiona
Formatting is "re-applied" or something in case it gets deleted? O
should I resort to an all-macro solution? An all-macro solution woul
be possible to make, but I just want to know if the Conditiona
Formatting for a column can be locked or reapplied somehow

--
Lav
-----------------------------------------------------------------------
Lava's Profile: http://www.excelforum.com/member.php...fo&userid=2779
View this thread: http://www.excelforum.com/showthread.php?threadid=47941

macro to ensure conditional formatting
 

You can apply the formatting with code, so after your macro deletes the
entries, have it reapply the formatting.

You can get the basic code by recording a macro while applying the
conditional formatting manually.

Just be aware that when applying the formatting, relative references like C5
are relative to the activecell.

--
Regards.
Tom Ogilvy



"Lava" wrote in message
...

I've got a column where the entries are checked against all fields of a
column on another worksheet. If there's no match the entry is invalid
and colored red. This is accomplished by means of Conditional
Formatting:


Code:
--------------------
=EN(AANTAL.ALS(Referentie_Lijst;C5)=0;NIET(ISLEEG( C5)))
--------------------


I've also written a macro which does the checking, but for ALL entry
fields of that column; not just the one which was just entered. The
reason for this was that I did some testing with the sheet. At some
point I had so many test values that I simply selected them all and
pressed [del]. To my surprise the Conditional Formatting for the fields
of that particular column was deleted as well.

Is there a real problem as I got a macro which takes care of things at
the end by checking them all by means of a button? Well, the people who
will be using the sheet for entry wish for the immediate check per
entry.

Is there a way by means of a macro or so to ensure that the Conditional
Formatting is "re-applied" or something in case it gets deleted? Or
should I resort to an all-macro solution? An all-macro solution would
be possible to make, but I just want to know if the Conditional
Formatting for a column can be locked or reapplied somehow.


--
Lava
------------------------------------------------------------------------
Lava's Profile:
http://www.excelforum.com/member.php...o&userid=27793
View this thread: http://www.excelforum.com/showthread...hreadid=479410

macro to ensure conditional formatting
 

It's not so much that the macro deletes the entries (and therefore th
conditional formatting for that cell), but a more general delete
Suppose I've just entered 5 rows and decide I screwed things up an
select those 5 rows in their entirety and delete them.

Conditional Formatting works on the activeCell yeah. It's how it check
with each entry

--
Lav
-----------------------------------------------------------------------
Lava's Profile: http://www.excelforum.com/member.php...fo&userid=2779
View this thread: http://www.excelforum.com/showthread.php?threadid=47941

macro to ensure conditional formatting
 

In you example, I don't see where that would affect conditional formatting,
but I understand what you are saying.

Be as cautious as you feel is necessary.
use events such as selectionchange, change, or calculate to reapply all the
conditional formats.

--
Regards,
Tom Ogilvy


"Lava" wrote in message
...

It's not so much that the macro deletes the entries (and therefore the
conditional formatting for that cell), but a more general delete.
Suppose I've just entered 5 rows and decide I screwed things up and
select those 5 rows in their entirety and delete them.

Conditional Formatting works on the activeCell yeah. It's how it checks
with each entry.


--
Lava
------------------------------------------------------------------------
Lava's Profile:
http://www.excelforum.com/member.php...o&userid=27793
View this thread: http://www.excelforum.com/showthread...hreadid=479410

macro to ensure conditional formatting
 

Well that is part of my question; at least what I tried to convey with
my question, but maybe didn't mention clearly. How do I reapply
Conditional Formatting to a column, cell, etcetera by means of a macro?
Not sure how it's done with the VB code.


--
Lava
------------------------------------------------------------------------
Lava's Profile: http://www.excelforum.com/member.php...o&userid=27793
View this thread: http://www.excelforum.com/showthread...hreadid=479410

macro to ensure conditional formatting
 

as previously stated:

You can get the basic code by recording a macro while applying the
conditional formatting manually.

--
Regards,
Tom Ogilvy


"Lava" wrote in message
...

Well that is part of my question; at least what I tried to convey with
my question, but maybe didn't mention clearly. How do I reapply
Conditional Formatting to a column, cell, etcetera by means of a macro?
Not sure how it's done with the VB code.


--
Lava
------------------------------------------------------------------------
Lava's Profile:
http://www.excelforum.com/member.php...o&userid=27793
View this thread: http://www.excelforum.com/showthread...hreadid=479410