If in Excel the background of a cell is colored with conditional formatting, this color will not change if a different color is specified using cell properties. The color set with conditional formatting always takes precedence. However, it may sometimes be desirable to manually give a cell a different color regardless of the conditional formatting setting. To achieve this, use will have to be made of VBA.
Open the VBA editor screen with Alt+F11 and put the following function in any module (if no module is available yet, first add a module via Insert - & gt; Module):
Function CellFilled(rng As Range) As Boolean CellFilled = Range(rng.Address).Interior.Pattern <> xlNone End Function
This function checks whether a cell has a set background color (so not the cell color with conditional formatting). Then select the range that the manual overriding should apply to and to the conditional formatting, add a new rule based on a formula and put the following formula in it:
Cell A1 must be the first cell of the selected range. No further formatting needs to be set for this rule. Make sure that this is the first rule that is applied and tick the checkbox 'Stop if True’. Here is an example of what the conditional formatting screen might look like:
But this is still not enough for this to work properly. Changing a background color does not trigger an event (if necessary, a specific event can be created for this, see: Create CellColorChange event) and also having the workbook recalculated does not make the manually set color visible. This color only becomes visible after a full recalculation (Ctrl+Alt+F9) of the workbook. Manually pressing these keys after each color change is of course not an option, therefore the Selection_Change event is used. Select the relevant worksheet in the VBA editor and add the following code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.CalculateFull End Sub
This makes the color visible if another random cell is selected after setting the color. A disadvantage of this is that a complete recalculation is carried out with every cell selection change. This is not a problem with simple workbooks, but with complex workbooks with many formulas it can slow down. Then it is better to use an adapted code. For example, if the conditional formatting applies to A1: A10, then the full calculation can be set only when a cell in that range or adjacent cell is selected:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range("A1:B11")) Is Nothing Then Application.CalculateFull End If End Sub
If someone changes the cell color and then immediately selects another tab, the Selection_Change event will not occur. By adding the following code, the set colors are always displayed when the respective worksheet is opened:
Private Sub Worksheet_Activate() Application.CalculateFull End Sub
In this way it is possible to manually override the set conditional formatting. The file must of course be saved with macros enabled.
Questions / suggestions
Hopefully this article has helped you to override conditional formatting in Excel. If you have any questions about this topic or suggestions for improvement, please post a comment below.