Option Explicit
Public Const sRangeToCheck = "L71:X135,L467:X531,L1299:X1363"
Sub ColorRange(ByVal Target As Range, bReverse As Boolean)
Dim WS As Worksheet
Dim iColor As Long
Dim iColor2 As Long
Dim iRow As Long
Dim iRow2 As Long
Dim iRowStart As Long
Dim iLoop As Long
Dim sRangeToColor As String
Dim sCol As String
Dim sTemp() As String
Dim bColorFont As Boolean
Static sLastRange As String
Static sLastQV As String
Dim rIR As Range, rMARKER As Range
Dim rIRCREDIT As Range, rIntersect As Range
Set WS = Target.Parent
Set rIR = WS.Range("IR_Data")
Set rMARKER = WS.Range("Marker_Data")
Set rIRCREDIT = WS.Range("IRCredit_Data")
If Not Intersect(Target, rIR) Is Nothing Then
iRowStart = rIR(1, 1).Row
ElseIf Not Intersect(Target, rMARKER) Is Nothing Then
iRowStart = rMARKER(1, 1).Row
ElseIf Not Intersect(Target, rIRCREDIT) Is Nothing Then
iRowStart = rIRCREDIT(1, 1).Row
End If
iRow = Target.Row - iRowStart ' 467
iColor = 0
iColor2 = 0
bColorFont = False
If Not bReverse Then
Select Case Target.Value
Case "FC": iColor = 3
Case "BC": iColor = 45
Case "ADFEAT": iColor = 4
Case "AD": iColor = 6
Case "LL": iColor = 40
Case "ROP": iColor = 41
Case "AMD": iColor = 26
Case "MB": iColor = 31
Case "AAM": iColor = 8
End Select
If WS.Cells(rIR(1, 1).Row + iRow, Target.Column).Value > 0 Then
iColor = 22
iColor2 = 12
End If
' If WS.Cells(iRow + 71, sCol).Value > 0 Then iColor = 22
End If
If WS.Cells(rIRCREDIT(1, 1).Row + iRow, Target.Column).Value > 0 Then iColor2 = 12
If iColor2 > 0 Then
bColorFont = True
iColor2 = iColor
End If
' If WS.Cells(iRow + 71, sCol).Value > 0 Then iColor = 22
sCol = Split(Target.Address(), "$")(1)
If sLastRange <> vbNullString Then
WS.Range(sLastRange).Interior.ColorIndex = 0
If sLastQV <> vbNullString Then
iRow2 = ((Target.Row - iRowStart) * 18) + 12
Sheets("QUICKView").Range("C" & iRow2 & ":C" & iRow2 + 16).Interior.ColorIndex = 0
Sheets("QUICKView").Range("C" & iRow2 + 17).Interior.ColorIndex = 0
If bColorFont = True Then
Sheets("QUICKView").Range("C" & iRow2 + 17).Font.Bold = False
Sheets("QUICKView").Range("C" & iRow2 + 17).Font.ColorIndex = 1
End If
End If
End If
sRangeToColor = CStr(sCol & iRow + 5 & "," & sCol & iRow + 71 & "," & sCol & iRow + 137 & "," & _
sCol & iRow + 203 & "," & sCol & iRow + 269 & "," & sCol & iRow + 335 & "," & _
sCol & iRow + 401 & "," & sCol & iRow + 467 & "," & sCol & iRow + 533 & "," & _
sCol & iRow + 599 & "," & sCol & iRow + 669 & "," & sCol & iRow + 741 & "," & _
sCol & iRow + 812 & "," & sCol & iRow + 883 & "," & sCol & iRow + 954 & "," & _
sCol & iRow + 1025 & "," & sCol & iRow + 1096 & "," & sCol & iRow + 1167 & "," & _
sCol & iRow + 1233 & "," & sCol & iRow + 1299 & "," & sCol & iRow + 1365 & "," & _
sCol & iRow + 1436 & "," & sCol & iRow + 1507 & "," & sCol & iRow + 1573 & "," & _
sCol & iRow + 1644 & "," & sCol & iRow + 1715 & "," & sCol & iRow + 1786 & "," & _
sCol & iRow + 1857 & "," & sCol & iRow + 1928)
WS.Range(sRangeToColor).Interior.ColorIndex = iColor
sLastRange = sRangeToColor
' iRow = Target.Row - 455
iRow = ((Target.Row - iRowStart) * 18) + 12
Sheets("QUICKView").Range("C" & iRow & ":C" & iRow + 16).Interior.ColorIndex = iColor
Sheets("QUICKView").Range("C" & iRow + 17).Interior.ColorIndex = iColor2
If bColorFont = True Then
Sheets("QUICKView").Range("C" & iRow + 17).Font.Bold = True
Sheets("QUICKView").Range("C" & iRow + 17).Font.ColorIndex = 3
End If
sLastQV = "C" & iRow & ":C" & iRow + 17
End Sub