| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting - Range
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional Formatting - Range
Hi Monica,
Range(Target, Target.Offset(0, 9)).Interior.ColorIndex = 18 Regards, OssieMac "Monica" wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional Formatting - Range
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" "Monica" wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional Formatting - Range
You might not need to use the Set since the range changes with the target.
"Monica" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional Formatting for a Range | New Users to Excel | |||
| Conditional Formatting with range | Excel Worksheet Functions | |||
| Conditional Formatting with range | Excel Worksheet Functions | |||
| conditional formatting using a range | Excel Discussion (Misc queries) | |||
| Conditional Formatting for a Range | Excel Discussion (Misc queries) | |||
Linear Mode
