Conditional Formatting - Range

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How can I make the color coding apply to the cell as well as the next 9
columns in that row.



Private Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range
Dim CellVal As String
If Target.Cells.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
CellVal = Target
Set WatchRange = Range("A1:c100") 'change to suit

If Not Intersect(Target, WatchRange) Is Nothing Then
Select Case CellVal
Case "Sheraton"
Target.Interior.ColorIndex = 18
Case "Covance"
Target.Interior.ColorIndex = 10
Case "Cronkite"
Target.Interior.ColorIndex = 6
Case "Intel"
Target.Interior.ColorIndex = 46
Case "Other"
Target.Interior.ColorIndex = 45
Case "Freescale"
Target.Interior.ColorIndex = 32
 
Hi Monica,

Range(Target, Target.Offset(0, 9)).Interior.ColorIndex = 18

Regards,

OssieMac
 
I didn't test this but it should work.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range
Dim CellVal As String
If Target.Cells.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
CellVal = Target
Set WatchRange = Range("A1:c100") 'change to suit
Set cRng = Range(Target.Offset(0, 0), Target.Offset(0, 9))
If Not Intersect(Target, WatchRange) Is Nothing Then
Select Case CellVal
Case "Sheraton"
cRng.Interior.ColorIndex = 18
Case "Covance"
cRng.Interior.ColorIndex = 10
Case "Cronkite"
cRng.Interior.ColorIndex = 6
Case "Intel"
cRng.Interior.ColorIndex = 46
Case "Other"
cRng.Interior.ColorIndex = 45
Case "Freescale"
 
Back
Top