CF highlighting ignoring CF text color

[andrewb90]

Hello all,

I have this code which has worked wonderfully for me to highlight lines on my sheet:

Option ExplicitConst MyAreas = "B6:M23,B25:M42,B44:M61,B63:M80,B82:M109,B111:M124,H126:M131"
Dim a, MyCol As Collection, rng As Range, x As Range
 
' Highlighting with Conditional Formatting
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim i As Long
  If Application.CutCopyMode Then Exit Sub
  If MyCol Is Nothing Then
    ' Setup MyCol only once first time
    Set MyCol = New Collection
    For Each a In Split(MyAreas, ",")
      MyCol.Add Range(a)
      ' Clear CF highligtings in each area for the first time
      Range(a).FormatConditions.Delete
    Next
  End If
  If Not x Is Nothing Then
    ' Clear the previous CF highlighting
    x.FormatConditions.Delete
  End If
  For Each x In MyCol
    ' Check intersection
    Set rng = Intersect(Target, x)
    If Not rng Is Nothing Then Exit For
  Next
  If Not x Is Nothing Then
    ' Highlight row of MyAreas via CF
    i = ActiveCell.Interior.ColorIndex
    Set x = x.Rows(rng.Row - x.Row + 1)
    With x.FormatConditions.Add(Type:=2, Formula1:=1)
      .Interior.ColorIndex = IIf(i < 0, 40, i + 1)
      .Font.Bold = True
    End With
  End If
End Sub

My only drawback is that I can't use other Conditional Formatting within my ranges. Is it possible to change this so that instead of just clearing all conditional formatting, it only clears the bold and fill color? I have a need to use additional conditional formatting to change the text color in select cells and this code just keeps clearing it. I can't seem to figure out how to get around this

 

[andrewb90]

bump

 

[andrewb90]

anybody?

 

[andrewb90]

bump

 

[andrewb90]

Perhaps my best bet is to cut my range short?