Solved: Temporarily Remove Conditional Formatting

[YellowLabPro]

I have CF on particular cells. Doing some error checking, I want to highlight a cell w/ yellow that has a CF color already in there.
Can I temporarily disable the CF for this target cell?
Or, will I have to remove the CF to color the interior?


For i = 6 To lLrw
If Range("G" & i).Value = "" Then
MsgBox """Stop!""" & Chr(32) & Chr(32) & "Missing The Brand Name!"
Range("G" & i).Select
Range("G" & i).Interior.Color = vbYellow
End

[YellowLabPro]

Followup to Q1-
The object browser shows xlDialogConditionalFormatting as a constant. There is no help on the item.
How should I find info/help on using this. I have not used members of XlBuiltinDialog before.

[rory]

The name is your best clue for that - it's the argument to send to Application.Dialogs() to bring up the Conditional Formatting dialog. I don't think that's what you want here though.
The easiest thing would be to apply a format not specified by the conditional formatting - change the border colour or something similar. Otherwise, you would need to store all the format conditions, delete them from the range, and somehow decide when to reapply them later. Or, if there are only 2 existing ones, you would need to add a new one and modify the existing ones so that your new condition took precedence over the others.
HTH

[Bob Phillips]

How about shunting the existing condition down a slot, and adding a new, always TRUE condition? Of course it won't work if you are already using all 3 conditions.

[YellowLabPro]

Good Morning Rory and Bob,
Thanks for both your answers.
In working through this, I discovered an error on my part.... I will explain further below.

Rory- good stuff. I played w/ that and see how it showed the different dialog arguments. I have some more work to do on this still...

Bob, yes get what you mean.... depending on the outcome this may be the route.

Both answers helped me to resolve.
I reworked my current conditional formatting w/ a slightly more elaborate formula w/in the CF.

The other thing I did discover from your answer Rory was there was a way to create/add or modify conditional formatting and is also applicable to Data Validation.
I did not find it by searching conditional formatting, but by luck using the .format and then .formatconditions was available. I did some searching around and it got me this help file:

///////////////////////////////////////////////////////////////////////////////////
The FormatConditions collection can contain up to three conditional formats. Each format is represented by a FormatCondition (mk:@MSITStore:C:\Program%20Files\Microsoft%20Office\OFFICE11\1033\vbaxl10. chm::/html/xlobjFormatCondition1.htm) object.
Using the FormatConditions Collection

Use the FormatConditions property to return a FormatConditions object. Use the Add (mk:@MSITStore:C:\Program%20Files\Microsoft%20Office\OFFICE11\1033\vbaxl10. chm::/html/xlmthAdd1.htm) method to create a new conditional format, and use the Modify (mk:@MSITStore:C:\Program%20Files\Microsoft%20Office\OFFICE11\1033\vbaxl10. chm::/html/xlmthModify1.htm) method to change an existing conditional format.
///////////////////////////////////////////////////////////////////////////////////

Now I know I am potentially sticking my head out on the chopping block here... <g>, but I have to comment that this is very frustrating. No help is linked directly to Conditional Formatting in the help file, yet all the information one needs to use conditional formatting is under the heading of FormatCondtions....gggggggrrrrrrrrrrrrrrrrrhhhhhhhhhhhhhhh

And what is also confusing is in the explanation portion- it shows the Add Method and the Modify Method....

But then under the Methods link up top, it shows the Modify Method and the Delete Method and not the Add Method; and if you go up one level to the collection it leaves out the Modify Method, and shows the Add, Delete and adds a new one Item Method.

This appears to be very disjointed in my opinion.

I recognize I am still the NKOTB, "new kid on the block", but to have to fight through all this is rubbish.

I know this is a vast beast, changing and evolving but as the help file evolves it should be availabe for an update to the program or a stand alone item.

If there is help .chm file from Excel 2007 and someone would share it, I would be most grateful.

Rant done!

Hope some other suffering fool finds this post and saves the time looking for help on Conditional Formatting :)

[Bob Phillips]

Doug,

You are not getting that you are dealing with a collection, FormatConditions, and an object within that collection, FormatCondition in the singular.

Collection only has an Add, Item, and Remove method, and in the implementation of the FormatConditions collection, they have not included a Remove method. I am not sure why, using a delete on the object is fine in itself but it is not intuitive that you are removing that from the collection that the object is within, but there you are. But collection has no modify method, and it seems logically right to me that it is the formatcondition object that is modified, not the item within the formatconditions collection.

[Bob Phillips]

BTW, what do you think that the 2007 help file will do for you?

[YellowLabPro]

Bob,
Overall I do understand the idea of collections and members. I do see there is the collection and there is the member of the collection in this case. I do not however have a clear distinction between the two yet.... in this case.
The Collection:


Represents the collection of conditional formats for a single range. The FormatConditions collection can contain up to three conditional formats. Each format is represented by a FormatCondition (mk:@MSITStore:C:\Program%20Files\Microsoft%20Office\OFFICE11\1033\VBAXL10. CHM::/html/xlobjFormatCondition1.htm) object.

The member:


Represents a conditional format. The FormatCondition object is a member of the FormatConditions (mk:@MSITStore:C:\Program%20Files\Microsoft%20Office\OFFICE11\1033\VBAXL10. CHM::/html/xlobjFormatConditions1.htm) collection. The FormatConditions collection can contain up to three conditional formats for a given range.

What I get from this is that a particular range can have up to three conditions, the object is one of these three conditions.

If clarity is needed, feel free to "learn me".

What my frustration was based on was that searching on ConditionalFormatting yielded nothing, but by typing Format.... led me to the help I was after initially and was only by luck I found this. By typing Format, I had no notion I was going to end up there.
As I change the way I think about this stuff, I can already see how I need to change the way I think about things..... (make sense?).

But a better help file would help.

I have seen in 2007 help that searching for topics is better targeted and direct than in 2003.
I don't have a topic off the top of my head, but have seen it- so it must be true <G>.

thanks for your help

[rory]

The most irritating thing is that you can't (at least in 2002 or prior) create (or grab) a FormatCondition and then add/apply it to a range. It would make your problem much easier!

[Bob Phillips]

Doug,

You are not getting that you are dealing with a collection, FormatConditions, and an object within that collection, FormatCondition in the singular.

Collection only has an Add, Item, and Remove method, and in the implementation of the FormatConditions collection, they have not included a Remove method. I am not sure why, using a delete on the object is fine in itself but it is not intuitive that you are removing that from the collection that the object is within, but there you are. But collection has no modify method, and it seems logically right to me that it is the formatcondition object that is modified, not the item within the formatconditions collection.

[rory]

I don't particularly like 2007 help, based on my limited usage so far.
You could have found the info by typing 'Conditional formatting' in the VB Help (at least in 2002) which would take you to the Formula1 and Formula2 properties, and the example shows the FormatConditions(1) syntax. But generally MS help works best when you already know what you are looking for - particularly in the MSKB!

[YellowLabPro]

Rory,
I should have tried that.
But in the initial reply from you I found the Dialog

Application.Dialogs(xlDialogConditionalFormatting).Show

I started my search in the object browser and then the VBA help file for
ConditionalFormatting, not Conditional Formatting w/ the space, which yielded no results, hence why I found nothing.

There was method to my madness it was just wrong. I eat a lot of crow... still does not taste good...

Precursor: I am probably sticking head out on the chopping block

I guess I need to learn more patience and search better.... moral of the story

thanks for showing me what to search on in the VBA help file...

[rory]

When searching in the object browser, I generally start out with a pretty generic search (e.g. here I would have just searched for 'condition' or 'format') so as to try and hit all possibilities. Sometimes you then need to narrow it down a bit, but usually there isn't too much to sift through.

[YellowLabPro]

That is very good advice- thanks