Conditional Formatting - Range

[Monica]

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

 

[OssieMac]

Hi Monica,

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

Regards,

OssieMac

 

[JLGWhiz]

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"

 

[JLGWhiz]

You might not need to use the Set since the range changes with the target.