Randomize tab colors when new sheets are added, and link color to formatting

[Arnold]

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

 

[Gary Keramidas]

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

 

[Arnold]

Thanks Greg.

I think I'm going to leave it be for now--not enough time to figure
this one out.