tsousa
02-17-2005, 04:41 PM
The information is displayed on a calendar made in excel (with the design of a regular calendar), were you have one column for the weekday of the month another one for the values of hotel occupancy for that day, and this repeats for the other columns.
On the cell I want to colour I have already a logical formula that gives me a text code (like TB1, Tb2 and so on...)
I want to attach to each text code (in my example are price tables) a color, I have 5 price tables.
I have tried to use the code below plus the module with no success. (I am only testing the first month)
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Cells.Count > 1 Then Exit Sub 'quit if more than 1 cell is selected
If Not Intersect(Target, [b4:o9]) Is Nothing Then
Select Case .Value
Case "TB1"
.Interior.ColorIndex = 40
Case "TB2"
.Interior.ColorIndex = 3
Case "TB3"
.Interior.ColorIndex = 4
Case "TB4"
.Interior.ColorIndex = 5
Case "TB5"
.Interior.ColorIndex = 6
Case Else
.Interior.ColorIndex = 0
End Select
End If
End With
End Sub
Module
Option Explicit
Sub RunOncePlease()
Dim cel As Range, rng As Range
Set rng = Range("A2:H366") 'will assume this is the activesheet
Select Case cel.Value
With cel.Interior
Case "TB1": .ColorIndex = 40
Case "TB2": .ColorIndex = 3
Case "TB3": .ColorIndex = 4
Case "TB4": .ColorIndex = 5
Case "TB5": .ColorIndex = 6
Case Else: .ColorIndex = 0
End With
End Select
End Sub
Error when i run the macro: with cel.interior statement or lable is not valid by reference to first case
I really do not know how to work it out, if someone could help.
Thank You
Teresa
On the cell I want to colour I have already a logical formula that gives me a text code (like TB1, Tb2 and so on...)
I want to attach to each text code (in my example are price tables) a color, I have 5 price tables.
I have tried to use the code below plus the module with no success. (I am only testing the first month)
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Cells.Count > 1 Then Exit Sub 'quit if more than 1 cell is selected
If Not Intersect(Target, [b4:o9]) Is Nothing Then
Select Case .Value
Case "TB1"
.Interior.ColorIndex = 40
Case "TB2"
.Interior.ColorIndex = 3
Case "TB3"
.Interior.ColorIndex = 4
Case "TB4"
.Interior.ColorIndex = 5
Case "TB5"
.Interior.ColorIndex = 6
Case Else
.Interior.ColorIndex = 0
End Select
End If
End With
End Sub
Module
Option Explicit
Sub RunOncePlease()
Dim cel As Range, rng As Range
Set rng = Range("A2:H366") 'will assume this is the activesheet
Select Case cel.Value
With cel.Interior
Case "TB1": .ColorIndex = 40
Case "TB2": .ColorIndex = 3
Case "TB3": .ColorIndex = 4
Case "TB4": .ColorIndex = 5
Case "TB5": .ColorIndex = 6
Case Else: .ColorIndex = 0
End With
End Select
End Sub
Error when i run the macro: with cel.interior statement or lable is not valid by reference to first case
I really do not know how to work it out, if someone could help.
Thank You
Teresa