I need to override a color fill set by Conditional Formatting

I'm working in Excel 2010.
I'm working with a spreadsheet in which each row contains information on a specific DVD (UPC code, Item Name, Price, Description, etc.).
Each row has three Conditional Formats which are triggered by the contents of a cell in that row which indicates the disc type.

1. Condition 1: If the trigger cell's value is "Blu-ray" the entire row is filled in blue (imaginative, I know)
2. Condition 2: If the trigger cell's value is "Blu-ray / DVD" the entire row is filled in lavender
3. Condition 3: If the trigger cell's value is "DVD-R" the entire row is filled in green

So far, so good. This part works perfectly, and allows us to quickly distinguish between formats.

Here's the hitch:

We need to be able to allow a particular user to manually highlight certain product rows quickly and easily.
Unfortunately, the Conditional Formatting always takes precedence over a manual fill.

We can't simply clear the formatting and then fill, as other users (including me) still need it in place.

I know the simplest way to do this would be to add another layer of conditional formatting and reference a "helper" column which our user could just put an "x" into.
That said, I can't add a helper column at the front of the sheet, because it would mess up literally every macro in the sheet (and I'm not going to rewrite them), and there are way too many columns to put it at the end.

What I need is something which will disable the conditional formatting in a given row if a color is manually filled in.
I just don't know how to get there. I'm thinking something in VBA that essentially says If Range("A2:BT2").InteriorColor = Orange Then ConditionalFormating = False (and yeah, I know this isn't real code).

I'm hitting the limits of my knowledge, and this is evidently a pretty esoteric problem, as I'm not turning up many ideas.

Please tell me there's an easy, obvious solution I'm just overlooking.

~Gene

Originally Posted by ChemistB

Suggestion:
Insert a Column A where user can insert an "X"
Then have a conditional formatting rule, #1 Rule with Stop if true, if $A2="X" format yellow (or whatever)
Otherwise, I do not believe conditional formatting can be overruled.

Well . . . uh, yeah, that would work, except I can't add Column A (or B, or C) without messing up the macros in the sheet which are keyed to specific columns.

Thanks for the reply, though.

Second line of thought:
Is there a way to implement a Conditional Format where the condition is based on the text format in a designated cell?

By this I mean something like "Format Row 2 -- Fill Yellow" if A2 is Bold?
That way, my end user could just bold the first cell of each row with a simple mouse-click, and highlight the row she wants (simple is the operative word here, in this case).

~Gene

Originally Posted by ChemistB

In this sheet, I select the data and then create a defined name "testing"

=CELL("format",Sheet1!$A1)

Then I placed this code in the sheet module (right click on sheet tab and "view code")

Please Login or Register  to view this content.

Then the Conditional formatting is
=testing = "F0"

Explanation: The code converts the target cell in column A from text formatted to Number formatted or back when the user double clicks on the cell. The conditional formatting turns the row blue if the cell in Column A is number formatted. There's definitely a glitch in that it doesn't turn the whole row blue until you click on a different cell but I'm not good enough with VBA to fix that. :/
See attached

Thanks, Chemist!
I can see how it's working in your example. Brilliant and effective.
I'm having a little trouble making it work in my sheet.
The double-click toggle works, although I can't use Target.Column = 1, because my column A is a UPC code (things get ugly if I change the format); can I go with Target.Column = 2, (Column B) which is a text field?

I've never used a "defined name" before, so I'm a little unclear on the meaning of =CELL("format",Sheet1!$A1). Where do I put that formula?
I'll have to research it this weekend. (I'm guessing that's why the Conditional Format isn't working for me yet).

~Gene

Huzzah!
I made it work in my sheet. Thank you for a great work-around to a peculiar problem.

For anyone in a similar boat (is there anyone?) It took me a little while to figure out that, since my sheet has headers in Row 1, and Column A needs to remain in a Number format, I needed the Defined Name reference "=CELL("format",Sheet1!$A1)" from your example to actually be =CELL("format",Sheet1!$B2).

Also, I was trying to set "Refers to" the whole range of my sheet($B$:$BH150), instead of 1 target cell in Column B.

Thanks again, ChemistB!

~Gene

I know this is an old post, but can't you just click cells you want to override, click Conditional Formatting,Clear Rules, Clear Rules for selected cells?

Thank you so much for this. You have solved a big issue I was having with my conditional formatting.