The following Excel VBA routine and supporting functions supply the functionality to highlight multiple rows based on groups of numbers in the first column of a data range, a facility that is otherwise achievable only with conditional formatting. Any number of columns and rows may be selected, though I haven't tested performance on large tables.
The code is straightforward, looping through the cells in the selected range and applying a new color when the value in the first column changes as the program proceeds down the range.
The color selection scheme is very basic. Colors equidistant in the spectrum supported by Excel (2007+) are selected based on the number of distinct colors set in the program (currently 16) and then assigned randomly to the row groupings in the data table.
For dark colors, the numbers or text in the cells are set in white for contrast.
The two supporting functions supply the fill color and font color codes to the main routine.
' Set the fill color of rows in a selected range based on the values
' in the first column of the range.
Dim Rng As Range, Rng2 As Range
Dim Cell_ As Range
Dim PriorCellValue As Variant
Dim CellColor As Long, FontColorIdx As Long
Dim NumberOfColors As Long
.EnableEvents = False
.DisplayAlerts = False
.ScreenUpdating = False
Set Rng = Selection
NumberOfColors = 16 '####### SET NUMBER OF COLORS HERE #######
For Each Cell_ In Rng.columns(1).Cells
If Cell_.Value <> PriorCellValue Then
CellColor = GetColorNumber(NumberOfColors)
FontColorIdx = GetFontColorIndex(CellColor) '
Set Rng2 = Range(Cell_, Cell_.Offset(0, Rng.columns.Count - 1))
.Color = CellColor
.TintAndShade = 0.5 '####### SET TINTING AND SHADING HERE #######
.Font.ColorIndex = FontColorIdx
PriorCellValue = Cell_.Value
.EnableEvents = True
.DisplayAlerts = True
.ScreenUpdating = True
Function GetColorNumber(NumberOfColors As Long) As Long
' Returns a color number randomly chosen from the number of
' colors specified. This function will not work in Excel versions
' prior to 2007, because of limits on the number of available
Dim Step As Long
Dim NumberOfExcelColors As Long
NumberOfExcelColors = 16276000 'approximately
Step = Fix(NumberOfExcelColors / NumberOfColors)
GetColorNumber = WorksheetFunction.RandBetween(1, NumberOfColors) * Step
' The Randbetween function is from the Excel Analysis ToolPak. If it is
' unavailable the following formula can be substituted:
' =INT((upperbound - lowerbound + 1) * RAND() + lowerbound)
Function GetFontColorIndex(BackgroundColor As Long) As Integer
' Returns color index for dark grey or white, which the function selects
' to contrast with the cell fill color.
Dim R As Long, G As Long, B As Long
Dim FontThreshold As Double
Dim Brightness As Double
R = BackgroundColor Mod 256
G = (BackgroundColor \ 256) Mod 256
B = (BackgroundColor \ 256 \ 256) Mod 256
FontThreshold = 130
Brightness = Sqr(R * R * 0.241 + G * G * 0.691 + B * B * 0.068)
If Brightness < FontThreshold Then
GetFontColorIndex = 2 'white
GetFontColorIndex = 49 'dark (1 is black)
' Long decimal to RGB color conversion algorithm published by Siddharth Rout
' at http://social.msdn.microsoft.com/Forums/en/exceldev/thread/df8a1e1e-e974
' -4a9c-938a-da18ae9f5252. The formula for perceived brightness of RGB colors
' is available in various forms on the Internet, perhaps earliest at