Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Indirect - how to ensure the formatting is transferred | Excel Discussion (Misc queries) | |||
Macro Conditional Formatting | Excel Worksheet Functions | |||
Conditional Formatting in a Macro | Excel Discussion (Misc queries) | |||
Conditional Formatting Macro | Excel Programming | |||
macro for conditional formatting | Excel Programming |