andrewb90
Well-known Member
- Joined
- Dec 16, 2009
- Messages
- 1,077
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:
Any help would be greatly appreciated!
Andrew
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:
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
Last edited: