Conditional Formatting - Multiple Rules Per Range (Macro) Help Please!

[brendanh]

Hey all,

I've recently created an Excel sheet with a bunch of conditional formatting to detect incorrect/suspicious entries, etc.
Unfortunately I ran into issues with Excel splitting the conditional formatting ranges when people inserted rows and copied data (http://stackoverflow.com/questions/1...ing-insert-row - Similar Problem).

Ultimately, my solution is to have a macro to delete all conditional formatting, and then re-insert it, this will fix my issues.

I'm having problems applying multiple rules to a single range.
I'm not a VisualBasic native (yet) - so it is probably something really basic.

Please Login or Register  to view this content.

This code basically detects for columns A and B, if there is a value in the next cell, and highlights the cell blue if it is empty. (i.e. If A1 is blank, but B1 is not, A1 should be blue as an error warning).

I run into issues with the two conditional formats on a single range. As you can see, I have two in there for the column B range, but only one of them will work at a time. Both formulas are valid, but Excel uses the first and ignores the second. (Note: The difference in the formulas is the "RC[-1]" and the "RC[1]" - which changes the cell it is 'watching' from left, to right)


I have multiple conditional formats on single ranges, so I need to know:

How can I set multiple conditional formats on a single range - without secondary format rules being ignored?


I really appreciate your help, it is great to be here and I look forward to learning more!
Thanks!

[kvsrinivasamurthy]

For the Range("B8:B1048576") both the formulas are same.

[brendanh]

For the Range("B8:B1048576") both the formulas are same.

Thanks for taking the time to check it out. As per OP
'(Note: The difference in the formulas is the "RC[-1]" and the "RC[1]" - which changes the cell it is 'watching' from left, to right)'

[kvsrinivasamurthy]

Try this code.

Please Login or Register  to view this content.

[brendanh]

Great - that works perfectly! I'm guessing it comes down to the ".FormatConditions(Range("B8:B10").FormatConditions.Count).SetFirstPriority"
Can I ask what exactly this does?

[kvsrinivasamurthy]

I guess that this command sets the priorirty for CF. If no priority is set only one condition will be accepted.

I request experts to explain more about this.

[brendanh]

And of course today I run into more issues!
When I try to run my format macro

Run-time error '1004':
Application-defined or object-defined error.

I've put asterisks on the lines that are highlighted by the debugger.
I can't for the life of me figure out what is wrong - this used to work!

Any thoughts?

Please Login or Register  to view this content.

[brendanh]

Ah - this had to do with me activating protection on the workbooks.
Is there any way to have protection, but allow validation edits?