priccijr
12-11-2006, 11:20 AM
I'm using the code below and was wondering what to modify if I need the entire row from A to H to change color depending on the value in column H?
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 "QUALITY"
Cell.Interior.ColorIndex = 3
Cell.Font.Bold = True
Case "CRITICAL"
Cell.Interior.ColorIndex = 3
Cell.Font.Bold = True
Case "MEDIUM"
Cell.Interior.ColorIndex = 6
Cell.Font.Bold = True
Case "LOW"
Cell.Interior.ColorIndex = 20
Cell.Font.Bold = True
Case "OK"
Cell.Interior.ColorIndex = 35
Cell.Font.Bold = True
Case Else
Cell.Interior.ColorIndex = xlNone
Cell.Font.Bold = False
End Select
Next
End Sub
TIA
-Pete
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 "QUALITY"
Cell.Interior.ColorIndex = 3
Cell.Font.Bold = True
Case "CRITICAL"
Cell.Interior.ColorIndex = 3
Cell.Font.Bold = True
Case "MEDIUM"
Cell.Interior.ColorIndex = 6
Cell.Font.Bold = True
Case "LOW"
Cell.Interior.ColorIndex = 20
Cell.Font.Bold = True
Case "OK"
Cell.Interior.ColorIndex = 35
Cell.Font.Bold = True
Case Else
Cell.Interior.ColorIndex = xlNone
Cell.Font.Bold = False
End Select
Next
End Sub
TIA
-Pete