macro to ensure conditional formatting

[Lava]

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:

Please Login or Register  to view this content.

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.

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[Lava]

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.

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[Lava]

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.

[Tom Ogilvy]

as previously stated:

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

--
Regards,
Tom Ogilvy


"Lava" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>