Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Randomize tab colors when new sheets are added, and link color to formatting
Hi All,
I have 2 main worksheets--Students and Schedules. With help from past posts and replies, I've made a module that allows users to archive the Schedules sheet, thus allowing them to compare what classes students have had in previous quarters or semesters. The start of the module is: Sheets("Schedules").Select Sheets("Schedules").Copy After:=Worksheets(Worksheets.Count) Sheets("Schedules (2)").Select ActiveWorkbook.Sheets("Schedules (2)").Tab.ColorIndex = -4142 'making the sheet's tab white Selection.FormatConditions.Delete 'to make older schedules more efficient. ActiveSheet.Name = Format(Now, "yyyy-mm-dd") 'renames the newly archived sheet with the date. and so on. Instead of coloring the new sheet's tab white, I'd like for Excel to select ANY color in it's pallete except for Red and Green--Red and Green are used for the Students and Schedules sheets. Here's the hard part... I'd like to add conditional formatting code that compares the data (student names) in the ranges on the newly archived sheet (named by a date yyyy-mm-dd)--and all previously archived sheets--with data in the same ranges on the current Schedule sheet. If the student names on the archived sheet are the same as those on the current Schedule sheet, then fill the cells on the Schedules sheet with the color of the archived sheet's tab. The newly archived sheet should take precedence--if a student name in the Schedules sheet is also in the most recently archived sheet as well as previously archived sheets, then just use the color from the most recently archived sheet. Note--A user will not archive more than 10 or so sheets in a year. And if that's not tricky enough, the student names in the ranges on the Schedules sheet can be sorted, so they may not be in the same cell as the student names in the archived sheet. Layout of the Schedules sheet: There are 13 classes = columns (B through N). There are 17 students slotted for each class. There are 6 ranges, one for each hour of the day (B15:N31, B47:N63, B79-N95, B111-N127, B143-N159, B175-N191). The hours are basically stacked on top of each other for printing purposes. Any help would be, as always, greatly appreciated. Thanks, Arnold |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Randomize tab colors when new sheets are added, and link color to formatting
maybe you can adapt something from this: Sub tbcolor() Dim ClrIndex As Long, i As Long For i = 1 To Worksheets.Count ClrIndex = Int((56 - 2 + 1) * Rnd + 2) If ClrIndex = 3 Or ClrIndex = 4 Then ClrIndex = ClrIndex + Int((52 * Rnd)) Worksheets(i).Tab.ColorIndex = ClrIndex Next i End Sub -- Gary "Arnold" wrote in message oups.com... Hi All, I have 2 main worksheets--Students and Schedules. With help from past posts and replies, I've made a module that allows users to archive the Schedules sheet, thus allowing them to compare what classes students have had in previous quarters or semesters. The start of the module is: Sheets("Schedules").Select Sheets("Schedules").Copy After:=Worksheets(Worksheets.Count) Sheets("Schedules (2)").Select ActiveWorkbook.Sheets("Schedules (2)").Tab.ColorIndex = -4142 'making the sheet's tab white Selection.FormatConditions.Delete 'to make older schedules more efficient. ActiveSheet.Name = Format(Now, "yyyy-mm-dd") 'renames the newly archived sheet with the date. and so on. Instead of coloring the new sheet's tab white, I'd like for Excel to select ANY color in it's pallete except for Red and Green--Red and Green are used for the Students and Schedules sheets. Here's the hard part... I'd like to add conditional formatting code that compares the data (student names) in the ranges on the newly archived sheet (named by a date yyyy-mm-dd)--and all previously archived sheets--with data in the same ranges on the current Schedule sheet. If the student names on the archived sheet are the same as those on the current Schedule sheet, then fill the cells on the Schedules sheet with the color of the archived sheet's tab. The newly archived sheet should take precedence--if a student name in the Schedules sheet is also in the most recently archived sheet as well as previously archived sheets, then just use the color from the most recently archived sheet. Note--A user will not archive more than 10 or so sheets in a year. And if that's not tricky enough, the student names in the ranges on the Schedules sheet can be sorted, so they may not be in the same cell as the student names in the archived sheet. Layout of the Schedules sheet: There are 13 classes = columns (B through N). There are 17 students slotted for each class. There are 6 ranges, one for each hour of the day (B15:N31, B47:N63, B79-N95, B111-N127, B143-N159, B175-N191). The hours are basically stacked on top of each other for printing purposes. Any help would be, as always, greatly appreciated. Thanks, Arnold |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Randomize tab colors when new sheets are added, and link color to formatting
Thanks Greg.
I think I'm going to leave it be for now--not enough time to figure this one out. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet formatting (fill colors & text colors) disappeared | Excel Discussion (Misc queries) | |||
How do I link text without losing (bold or color) formatting? | Excel Discussion (Misc queries) | |||
pivot chart colors- how to keep from changing when data elements are added/removed? | Charts and Charting in Excel | |||
formatting background color of a cell with custom colors | Excel Programming | |||
Color added to Dos | Excel Programming |