Manually overriding conditional formatting

Question

I have tried to allow manual overriding of conditional formatting using the advice from the following post:

http://answers.microsoft.com/en-us/office/forum/office_2007-excel/manual-override-of-conditional-formatting/717b6870-3127-4a6c-b24a-a3684ab02842?lc=4105&auth=1

I now have this function:

Function TestColor(MyRange As Range) As Boolean

     If Range(MyRange.Address).Interior.ColorIndex = xlNone Then TestColor = True

 End Function

Along with these 3 rules in order:

=NOT(TestColor(A1)) --> Stop If True

=MOD(ROW(0,2)=0   --> colors row with color1

=MOD(ROW(0,2)=1   --> colors row with color2

The alternate rows are being banded with the specified colors as desired. However, the first rule and the function seem to be having no effect whatsoever.

Where am I going wrong?

Answer 1

To turn conditional formatting on and off I usually set up some "On/Off" data validation drop down cells, then incorporate them into an =AND function in the conditional formatting, checking for whether they're "On", along with the original criteria.

This way, you can have as many rules as you like, with the ability to turn them on or off individually.

Answer 2

FWIW, I have never had any resounding success when relying upon Stop if true. Why not simply incorporate the boolean function into the CF rule formulas with AND()?

=AND(TestColor(A1), NOT(MOD(ROW(),2)))    ◄ banded color1

=AND(TestColor(A1), MOD(ROW(),2))             ◄ banded color2