Change code to use fill colors

[andrewb90]

Hello all,

I have the code that highlights my rows, however I would like to not use conditional formatting, but instead use fill color. Is that possible?

Option ExplicitConst MyAreas = "B5:V341"
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, 4, i + 1)
      .Font.Bold = True
    End With
  End If
End Sub

 

[Fishboy]

Hello all,

I have the code that highlights my rows, however I would like to not use conditional formatting, but instead use fill color. Is that possible?

Option ExplicitConst MyAreas = "B5:V341"
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, 4, i + 1)
      .Font.Bold = True
    End With
  End If
End Sub

Hi Andrew,

It is this part you need to change:

  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, 4, i + 1)
      .Font.Bold = True
    End With
  End If

I suspect you can just take out the whole conditional formatting stuff from this section and replace it with just something "like" this:

With x
.interior.colorindex = "Your ColorIndex Number" ' for whatever colour you want there.  3 Is red, 4 is green, 5 is blue, 6 is yellow, and so on...
.Font.Bold = True
End With

 

[andrewb90]

This turned the entire section red then did not remove the color after.

 

[Fishboy]

This turned the entire section red then did not remove the color after.

Sorry Andrew, I have only just noticed your reply. I think without seeing the document itself to make the rest of the code make sense, I am not too sure what else to suggest at this time.