Hello,
I have a seating chart that needs to have multiple colors to highlight certain restrictions. There are a total of 8 colors so VBA must be used.
I have simplified this to avoid using multiple tabs, but the restrictions are pulled from two separate columns and the text may or may not appear on the chart depending on the restriction.
I.e. If the seat is available it is shown on the seating chart as "Available". If the seat is not available for desk sharing, it is also shown on the seating chart as "No Desksharing".
All of the other codes restricting availablility by shift are not shown as text on the seating chart, but as the color restriction - Day, Swing, Grave, Swing or Grave, Day or Swing, and Grave or Day.
The following code has all the colors, but I don't know what is required to get pull the right codes from the columns in VBA. Any help would be appreciated and serves as a building block for future tweaks.
Private Sub Worksheet_Change(ByVal Target As Range)
If Application.Intersect(Target, Range("A1:bo16")) Is Nothing Then
Exit Sub
Else
Select Case Target.Value
'These two codes are available from column D of the Building 1 tab and are populated in the seating chart
Case Is = "Available"
Target.Interior.ColorIndex = 6 'Yellow
Case Is = "Deskshare"
Target.Interior.ColorIndex = 20 'Light Blue
'These six codes are available from column F in the Building 1 tab and are not populated in the seating chart by text, but by color only
Case Is = "Day"
Target.Interior.ColorIndex = 31 'Green
Case Is = "Swing"
Target.Interior.ColorIndex = 40 'Orange
Case Is = "Grave"
Target.Interior.ColorIndex = 47 'Purple
Case Is = "SwingGrave"
Target.Interior.ColorIndex = 45 'Bright Orange
Case Is = "DaySwing"
Target.Interior.ColorIndex = 43 'Olive
Case Is = "GraveDay"
Target.Interior.ColorIndex = 48 'Gray
Case Else
Target.Interior.ColorIndex = 2 'white
End Select
End If
End Sub
Display More
Thanks,
Rod