Looking to not use conditional formatting

[andrewb90]

Hello All,

I have a line highlighting code that I have been using for quite some time and it has served me well, however I now am encountering issues with needing other CF on my cells. How can I make this use traditional fill colors instead of conditional formatting?

Here is my current code:

Option Explicit
Const MyAreas = "D4:R18,D20:R34,D36:R50,D52:R66,D68:R82,D87:U111,D113:U137,D139:U146,D148:U156,D157:U157"
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, 8, i + 1)
      .Font.Bold = True
    End With
  End If

'1 BLACK
'2 WHITE
'3 RED
'4 BRIGHT GREEN
'5 BLUE
'6 YELLOW
'7 PINK
'8 LIGHT BLUE
'9 MAROON
'10 DARK GREEN
'11 DARK BLUE
'12 GUACAMOLE
'13 PURPLE
'14 TEAL
'15 GREY



End Sub

Any help would be greatly appreciated!

Andrew

 

[Rijnsent]

Hi Andrew,

you're trying to get rid of the Conditional Formatting? I am trying to understand what you are trying to achieve, but I fail to do so, could you maybe elaborate a bit?

Thanks,

Koen

 

[andrewb90]

Right now, the code looks at the row and it deletes any existing conditional formatting then applies new conditional formatting to that row (yellow or blue, or whatever color code I want) then when you click on a cell that is not on that row, the CF clears once again.
Basically I use it on long lists that I need to scroll through and it highlights the current row for me so that it is easy to see.
I am wanting to do the same thing but with normal Fill color and not conditional formatting.