Conditional Formatting - 2nd condition isn't overriding the first

[polarisking]

This macro deletes all existing CFs, then sets 2 new ones:

• The first, based on a MOD 2 test vs. Row #, sets zebra stripes (light blue)
• The second, based on the cell >3 in columns 1 and 4, colors the cell/Font.

It appears that the second condition isn't affecting cells (A4, A6, and D2) that were affected by the zebra striping. I'm assuming that successive conditions should override prior conditions - am I wrong?. I've attached the macro file.

Thanks in advance for any assitance/explanation.

Sub CondFormat()
  Dim ws  As Worksheet
  Dim cell  As Range
  Dim ctr  As Long
  Dim StartTimer  As Double
  Dim LastCol  As Long
  Dim LastRow  As Long
  Dim FirstRow  As Long
  
  Dim Rng_All  As Range
  Dim Rng_PortPct  As Range
  Dim Rng_ReconPortPct As Range
  Dim RR  As Range
'================================================================================
  StartTimer = Timer
  
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  Application.Calculation = xlCalculationManual
'================================================================================
  LastCol = Cells(5, Columns.Count).End(xlToLeft).Column
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  FirstRow = 1
  
  Set Rng_All = Range(Cells(FirstRow, 1), Cells(LastRow, LastCol))
  Set Rng_PortPct = Range(Cells(FirstRow, 1), Cells(LastRow, 1))
  Set Rng_ReconPortPct = Range(Cells(FirstRow, 4), Cells(LastRow, 4))
  Set RR = Union(Rng_PortPct, Rng_ReconPortPct)
'================================================================================
  Rng_All.FormatConditions.Delete
'
  Rng_All.FormatConditions.Add Type:=xlExpression, Formula1:= _
  "=MOD(ROW(),2)=0"
  'Rng_All.FormatConditions(Rng_All.FormatConditions.Count).SetFirstPriority
  'Rng_All.FormatConditions(1).SetFirstPriority
  With Rng_All.FormatConditions(1)
  .Font.Color = vbBlack
  .Interior.Color = RGB(218, 238, 243)
  End With
  Rng_All.FormatConditions(1).StopIfTrue = False
  
  RR.FormatConditions.Add Type:=xlExpression, Formula1:= _
  "=A1>3"
  With RR.FormatConditions(2)
  .Interior.Color = vbRed
  .Font.Bold = True
  .Font.Color = vbWhite
  End With
'================================================================================
  Application.ScreenUpdating = True
  Application.DisplayAlerts = True
  Application.Calculation = xlCalculationAutomatic
  
  MsgBox "Complete in " & Format(Timer - StartTimer, "#0.000") & " seconds"
  
End Sub

 

[Hui]

I rearranged the order of the CF's and got this


I don't think CF formats are cummulative, that is the second CF will override the first not add to it

 

[NARAYANK991]

Hi ,

The following article will make everything clear :

http://www.excelguru.ca/content.php?278-Understanding-How-Conditional-Formatting-Rules-Are-Applied

Narayan

 

[NARAYANK991]

Hi ,

Based on what I have understood from the above article :

1. Though the VBA statement is FormatConditions.Add , what we are doing is adding to the number of rules in existence , not to the number of rules which will be applied.

The number of rules which can be applied to a cell / range is only one. The first rule which is applicable in the top to bottom sequence will be the rule which will prevail.

2. We should therefore always try to make our rules themselves mutually exclusive , so that from a reading of the rules , it is clear that only one rule can apply at all times.

For example , if you change your first rule to :

=AND(MOD(ROW(),2)=0,OR(A1<=3,COLUMN()=2,COLUMN()=3))

this will ensure that your two rules are mutually exclusive. In this case , the order of the rules is immaterial.

Narayan