Add column to Row highlighting VBA

[andrewb90]

Hello All,

I have this code that I am using to highlight rows, I have been trying (unsuccessfully) to have it highlight the column (Row2:20) as well. Can anybody help?

Option ExplicitConst MyAreas = "C2:S14"
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
End Sub

Thanks,

Andrew

 

[andrewb90]

bump^