Tēnā koutou! I am trying to switch my conditional formatting into VBA because of the pesky way the normal method changes cell references, which can cause things to start failing as cells are deleted, added, moved, etc. (Whhyyyy can't it keep $e$12:$ca$137 instead of turning into something like =$E$13:$F$14,$E$15:$E$18,$H$13:$X$13,$H$15:$CA$15,$H$14:$Y$14,$AA$14:$CA$14,$AA$13:$AI$13,$G$16:$CA$16,$G$18:$CA$18,$G$17:$AI$17,$AK$17:$CA$17,$AK$13:$BB$13,$BD$13:$CA$13,$E$12:$CA$12,$E$19:$CA$137?!)

Anyways. /rant

I am running into an issue when I want to layer formats across cells based on multiple criteria. For example, I have two cells containing "298.101 Lecture" and "298.101 continued". I have a condition to set the fill background to pink if the cell contains "298.101". However, I also want to have a condition that makes the font italic and grey if the cell contains "continued". (I need heaps of variation on this theme.) So, the second cell should be BOTH pink filled and have grey, italicised font.

When I run this, it seems that the top condition always trumps the following ones, even if the top one doesn't set a specific interior format. Works fine in CF though.

VBA Code
Sub ConditionalFormatting()
'Definining the variables:
  Dim rng As Range

 'Fixing/Setting the range on which conditional formatting is to be desired
  Set rng = Range("e12", "ca137")

  'To delete/clear any existing conditional formatting from the range
   rng.FormatConditions.Delete
   
  'Defining and setting the criteria for each conditional format

Set condition1 = rng.FormatConditions.Add(xlTextString, TextOperator:=xlContains, String:="continued")
    With condition1
        .Font.Color = RGB(89, 89, 89)
        .Font.Italic = True
        
Set condition6 = rng.FormatConditions.Add(xlTextString, TextOperator:=xlContains, String:="298.")
    With condition6
        .Interior.Color = RGB(255, 217, 255)
   End With
END SUB
What VBA is giving me:
Screenshot 2024-03-15 130021.png

What normal CF gives me:
Screenshot 2024-03-15 130306.png