Excel Manual Formatting - Override Condtional Formatting Help

[DushiPunda]

Hello,
I have an event log at work and every other row is highlighted gray via conditional formatting using this:

=MOD(ROW(),2)=1

So, as the title says, I want manual formatting to override my conditional formatting. That is, if I want to highlight one of the rows that is already highlighted gray with a different color, then that color should show and not the gray.

I've found a way to do this, though I can't exactly remember where. Here's how it's set up:

Public Function TestColor(MyRange as Range) As Boolean[INDENT]If Range(MyRange.Address).Interior.Pattern = xlNone Then TestColor = True[/INDENT]
End Function

I then have another conditional formatting rule. This rule is the top rule, "Stop If True" is checked, and formatting is set to none. The rule formula:

=NOT(TestColor(A3))

This all works fine and dandy, but there are some quirks to it. Here's an example:
1. If I select on of the rows and and change the fill to, say, yellow.
2. Upon hovering over the color I want to select (obviously this only applies if I use the drop down to select the color and do not just click the button to use the currently selected color), my selection turns that color.
3. Upon clicking on the color, the row will be displayed as gray.
4. After that, if I enter edit mode on any cell (double click), and then exit edit mode by selecting another cell or tabbing out, the row I changed will update to the color I selected. (This is quirky; while testing this, I noticed that one time I did this with a cell that was not within my selection and the fill did not update. I've tested this numerous times since then and it's worked every time)

It is probably worth noting:
1. Resetting the original selection back to no fill immediately updates it to the expected gray fill.
2. Sometimes, usually the first time I try to change the fill on one of the gray lines, it will change to the new color immediately. Subsequent changes to other rows fall in line with my example above.

I've tried changing the fill color by all of the following methods, and they all behave the same way:
1. Selecting the drop down arrow next to the Fill Color button on the Home Ribbon, then selecting the color.
2. Clicking the Fill Color button directly on the Home Ribbon (if it is set to something other than "No Fill").
3. Right clicking on my selection and doing the same thing as #1/#2 using the Fill Color button on the small formatting box that appears above the right click window.
4. Right clicking on my selection and choosing Format Cells and selecting the color on the Fill tab and then hitting OK.

After some research, I've discovered that it's not possible to trigger events (i.e. Worksheet_Change) when the formatting of a cell (or cells) is changed. I tried a couple things with the Worksheet_SelectionChange event, but to no avail. Here's what I tried:

1. ActiveSheet.Calculate
2. Application.ScreenUpdating toggle (Application.ScreenUpdating = False (next line) Application.ScreenUpdating = True)

Again, neither of these worked. I'm looking for possible solutions to get this to act as expected, i.e.:
1. Select fill color
2. Correct fill color displays without any of the quirkiness noted above.

Thanks!

 

[DushiPunda]

Further Testing:

1. Changing the TestColor function to "<> xlNone" from "= xlNone" and alt-tabbing back to my workbook updates everything automatically (i.e. everything turns to no fill because my first rule triggers). If I do not alt-tab to switch back to my workbook but instead click on the workbook, it does not update. Sometimes, the display seems to get bugged and looks like hot garbage. The best way I can describe it is: create a macro that turns off ScreenUpdating, does a bunch of cell formatting, and then have something in the macro to intentionally error out (thus your left with ScreenUpdating turned off). The result is likely pretty similar to what I'm seeing. Anyway, upon entering edit mode of a cell and then exiting edit mode, everything returns to normal. Alternatively, going into Manage Rules for Conditional Formatting and, for example, unchecking and then checking the Stop If True box next to my first rule (so that I can get the Apply button enabled), then hitting Apply will update the display and return everything to normal.

2. I've also tried changing my "every other row" rule to the following, which did not change anything in terms of the quirkiness i'm experiencing:

=IF(TestColor(A3),MOD(ROW(),2)=1)

3. Along with #1, I've tried reversing the logic for everything, but it still resulted in the same quirkiness:
- Function: "<> xlNone Then Test Color = True" or "= xlNone Then TestColor = False"
- 1st Rule: "=TestColor(A3)"
- 2nd Rule: "=IF(NOT(TestColor(A3),MOD(ROW(),2)=1)", as well as keeping the original "=MOD(ROW(),2)=1" rule.

4. Removing my first rule altogether, and changing the "every other row" rule to "=IF(TestColor(A3),MOD(ROW(),2)=1)" also results in the quirkiness.