Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell coloring problem
Below is some code I use to color some cells based on text entered into the
cell, and it works fine. If the word Vacation is entered that cell and the one above it is changed to the color blue. But when the word is removed, only the cell with the word is changed back to no color. Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("A40:G57")) Is Nothing Then Select Case Left(Target.Text, 4) Case Is = "Sick" icolor = 38 Target.Offset(-1, 0).Resize(1, 1).Interior.ColorIndex = icolor End Select Select Case Left(Target.Text, 4) Case Is = "Vaca" icolor = 34 Target.Offset(-1, 0).Resize(1, 1).Interior.ColorIndex = icolor End Select End Select Target.Interior.ColorIndex = icolor End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell coloring problem
Try it like this. Strictly speaking the Case Else statement may not be needed
as icolor will be 0 by default if not set to anything but I have put it in for clarity. Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("A40:G57")) Is Nothing Then Select Case Left(Target.Text, 4) Case Is = "Sick" icolor = 38 Case Is = "Vaca" icolor = 34 Case Else icolor = 0 End Select Target.Offset(-1, 0).Interior.ColorIndex = icolor Target.Interior.ColorIndex = icolor End If End Sub Hope this helps Rowan "Patrick Simonds" wrote: Below is some code I use to color some cells based on text entered into the cell, and it works fine. If the word Vacation is entered that cell and the one above it is changed to the color blue. But when the word is removed, only the cell with the word is changed back to no color. Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("A40:G57")) Is Nothing Then Select Case Left(Target.Text, 4) Case Is = "Sick" icolor = 38 Target.Offset(-1, 0).Resize(1, 1).Interior.ColorIndex = icolor End Select Select Case Left(Target.Text, 4) Case Is = "Vaca" icolor = 34 Target.Offset(-1, 0).Resize(1, 1).Interior.ColorIndex = icolor End Select End Select Target.Interior.ColorIndex = icolor End If End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell coloring problem
Where is the code that sets it to no colour in that?
-- HTH Bob Phillips "Patrick Simonds" wrote in message ... Below is some code I use to color some cells based on text entered into the cell, and it works fine. If the word Vacation is entered that cell and the one above it is changed to the color blue. But when the word is removed, only the cell with the word is changed back to no color. Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("A40:G57")) Is Nothing Then Select Case Left(Target.Text, 4) Case Is = "Sick" icolor = 38 Target.Offset(-1, 0).Resize(1, 1).Interior.ColorIndex = icolor End Select Select Case Left(Target.Text, 4) Case Is = "Vaca" icolor = 34 Target.Offset(-1, 0).Resize(1, 1).Interior.ColorIndex = icolor End Select End Select Target.Interior.ColorIndex = icolor End If End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell coloring problem
Colors are a funny thing. They adjust to the users pallet. my index 34 may
not be you r ondex 34. you may want to check out RGB settings. In the meantime: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1:G57")) Is Nothing Then Select Case UCase(Left(Target.Text, 4)) Case Is = "SICK" Target.Offset(-1, 0).Resize(1, 1).Interior.ColorIndex = 38 Case Is = "VACA" Target.Offset(-1, 0).Resize(1, 1).Interior.ColorIndex = 34 Case Is = "" Target.Offset(-1, 0).Resize(1, 1).Interior.ColorIndex = xlColorIndexNone End Select End If End Sub HTH me "Patrick Simonds" wrote in message ... Below is some code I use to color some cells based on text entered into the cell, and it works fine. If the word Vacation is entered that cell and the one above it is changed to the color blue. But when the word is removed, only the cell with the word is changed back to no color. Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("A40:G57")) Is Nothing Then Select Case Left(Target.Text, 4) Case Is = "Sick" icolor = 38 Target.Offset(-1, 0).Resize(1, 1).Interior.ColorIndex = icolor End Select Select Case Left(Target.Text, 4) Case Is = "Vaca" icolor = 34 Target.Offset(-1, 0).Resize(1, 1).Interior.ColorIndex = icolor End Select End Select Target.Interior.ColorIndex = icolor End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell Coloring | Excel Discussion (Misc queries) | |||
Cell coloring | Excel Discussion (Misc queries) | |||
Cell Coloring | Excel Worksheet Functions | |||
Problem while coloring cells in excel through vb | Excel Discussion (Misc queries) | |||
Coloring problem | Excel Programming |