Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'd like to automatically highlight multiple rows based on the group of numbers in one column. So I would have a different row color for each unique family ID. Is there a way to do this without manually having to select a color in conditional formating? I have 400 families and 1000+ rows. Thanks for the help guys.

Example data:

FamilyID  Stock Number     Class
1922      1361427          Box
1922      5859184          Box
1922      1422784          Box
1922      1422694          Box
2196      1336358          Circle
2196      1336244          Circle
2196      675239           Circle
2205      57813            Square
2205      863453           Square
2220      30664            Square
2220      1336732          Square
2220      22874            Square
share|improve this question
    
Thanks for the edit Nixda. –  user184581 Jan 4 '13 at 17:43
    
EXCEL 2007 Thank you. –  user184581 Jan 4 '13 at 18:03
add comment

3 Answers

I took the code above and improved it so there is no chance that u might have following cell with the same color...enjoy :)

Dim LastColor As Long

Sub ColorSortedRange()

    LastColor = GetColorNumber(5)

   ' 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

   With Application
      .EnableEvents = False
      .DisplayAlerts = False
      .ScreenUpdating = False
   End With

   Set Rng = Selection
   NumberOfColors = 50                 '####### SET NUMBER OF COLORS HERE #######

   For Each Cell_ In Rng.Columns(1).Cells
       If Cell_.Value <> PriorCellValue Then
           CellColor = GetColorNumber(NumberOfColors, LastColor)
           LastColor = CellColor
           FontColorIdx = GetFontColorIndex(CellColor)                      '
       End If
       Set Rng2 = Range(Cell_, Cell_.Offset(0, Rng.Columns.Count - 1))
       With Rng2
           With .Interior
               .Color = CellColor
               .TintAndShade = 0.5    '####### SET TINTING AND SHADING HERE #######
           End With
           .Font.ColorIndex = FontColorIdx
       End With
       PriorCellValue = Cell_.Value
   Next

   With Application
      .EnableEvents = True
      .DisplayAlerts = True
      .ScreenUpdating = True
   End With

End Sub


Function GetColorNumber(NumberOfColors As Long, Optional OldColor As Long = 0) 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
   ' colors.

   Dim Step As Long
   Dim NumberOfExcelColors As Long

   NumberOfExcelColors = 16276000 'approximately
   Step = Fix(NumberOfExcelColors / NumberOfColors)
   GetColorNumber = WorksheetFunction.RandBetween(1, NumberOfColors) * Step

   If GetColorNumber = OldColor Then
    GetColorNumber = GetColorNumber(NumberOfColors, OldColor)
   End If


   ' 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)

End Function


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
   Else
       GetFontColorIndex = 49 'dark (1 is black)
   End If

   ' 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
   ' http://alienryderflex.com/hsp.html.

End Function
share|improve this answer
add comment

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.

Sub ColorSortedRange()

   ' 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

   With Application
      .EnableEvents = False
      .DisplayAlerts = False
      .ScreenUpdating = False
   End With

   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)                      '
       End If
       Set Rng2 = Range(Cell_, Cell_.Offset(0, Rng.columns.Count - 1))
       With Rng2
           With .Interior
               .Color = CellColor
               .TintAndShade = 0.5    '####### SET TINTING AND SHADING HERE #######
           End With
           .Font.ColorIndex = FontColorIdx
       End With
       PriorCellValue = Cell_.Value
   Next

   With Application
      .EnableEvents = True
      .DisplayAlerts = True
      .ScreenUpdating = True
   End With

End Sub


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
   ' colors.

   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)

End Function


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
   Else
       GetFontColorIndex = 49 'dark (1 is black)
   End If

   ' 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
   ' http://alienryderflex.com/hsp.html.

End Function
share|improve this answer
    
The answer to SO10455366 may point one way to access more than 16 colours. –  pnuts Jan 7 '13 at 16:21
    
COLOR BANDING in a nutshell If the column to be checked is B Create a helper column, say in column A A1: 0 A2: =MOD(OFFSET($A2,-1,0)+(OFFSET($B2,-1,0)<>$B2),2) Then set up the Conditional Formatting Select any cell on Row 1, then select the entire worksheet Format, Conditional Formatting condition 1, formulas is: =$A1=1 –  user184581 May 15 '13 at 0:10
add comment

Assuming FamilyID is in ColumnA, use a series of conditional formatting rules under Use a formula to determine which cells to format (of the kind =$a2=1922 (yellow), =$a2=2196 (green) etc.) in A2 and have each apply to the whole row.

SU528202 example

share|improve this answer
    
You are correct ColumnA contains Family ID. This solution is what I was trying to avoid. I have 400 families and 1000 rows. –  user184581 Jan 4 '13 at 18:25
3  
@user184581 This does answer your question as you worded it. You may want to edit the question to include the details about the number of families and rows. –  CharlieRB Jan 4 '13 at 19:32
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.