cmm0812
01-03-2008, 02:32 PM
I am using the code below (conditional formatting) and want to use it in a spreadsheet where the conditionally formatted cells reference other cells. Are there any suggestions as to how I can change this code to allow for that to happen?
Option Compare Text 'A=a, B=b, ... Z=z
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Dim Rng1 As Range
On Error Resume Next
Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1)
On Error GoTo 0
If Rng1 Is Nothing Then
Set Rng1 = Range(Target.Address)
Else
Set Rng1 = Union(Range(Target.Address), Rng1)
End If
For Each Cell In Rng1
Select Case Cell.Value
Case vbNullString
Cell.Interior.ColorIndex = xlNone
Cell.Font.Bold = False
Case 2008
Cell.Interior.ColorIndex = 18
Cell.Font.Bold = True
Cell.Font.ColorIndex = 2
Case 2009
Cell.Interior.ColorIndex = 40
Cell.Font.Bold = True
Cell.Font.ColorIndex = 1
Case 2010
Cell.Interior.ColorIndex = 43
Cell.Font.Bold = True
Cell.Font.ColorIndex = 1
Case 2011
Cell.Interior.ColorIndex = 36
Cell.Font.Bold = True
Cell.Font.ColorIndex = 1
Case 2012
Cell.Interior.ColorIndex = 31
Cell.Font.Bold = True
Cell.Font.ColorIndex = 2
Case 2013
Cell.Interior.ColorIndex = 41
Cell.Font.Bold = True
Cell.Font.ColorIndex = 2
Case Else
Cell.Interior.ColorIndex = xlNone
Cell.Font.Bold = False
End Select
Next
End Sub
Option Compare Text 'A=a, B=b, ... Z=z
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Dim Rng1 As Range
On Error Resume Next
Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1)
On Error GoTo 0
If Rng1 Is Nothing Then
Set Rng1 = Range(Target.Address)
Else
Set Rng1 = Union(Range(Target.Address), Rng1)
End If
For Each Cell In Rng1
Select Case Cell.Value
Case vbNullString
Cell.Interior.ColorIndex = xlNone
Cell.Font.Bold = False
Case 2008
Cell.Interior.ColorIndex = 18
Cell.Font.Bold = True
Cell.Font.ColorIndex = 2
Case 2009
Cell.Interior.ColorIndex = 40
Cell.Font.Bold = True
Cell.Font.ColorIndex = 1
Case 2010
Cell.Interior.ColorIndex = 43
Cell.Font.Bold = True
Cell.Font.ColorIndex = 1
Case 2011
Cell.Interior.ColorIndex = 36
Cell.Font.Bold = True
Cell.Font.ColorIndex = 1
Case 2012
Cell.Interior.ColorIndex = 31
Cell.Font.Bold = True
Cell.Font.ColorIndex = 2
Case 2013
Cell.Interior.ColorIndex = 41
Cell.Font.Bold = True
Cell.Font.ColorIndex = 2
Case Else
Cell.Interior.ColorIndex = xlNone
Cell.Font.Bold = False
End Select
Next
End Sub