Conditional Formatting - Range

M

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
 
O

OssieMac

Hi Monica,

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

Regards,

OssieMac
 
J

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"
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top