如何根据列单元格号突出显示多行？

Question 1

假设 FamilyID 在中ColumnA，使用下的一系列条件格式规则使用公式确定要格式化的单元格（例如 =$a2=1922 （黄色）、=$a2=2196 （绿色）等）并将A2每个应用于整行。

SU528202 示例

Answer

假设 FamilyID 在中ColumnA，使用下的一系列条件格式规则使用公式确定要格式化的单元格（例如 =$a2=1922 （黄色）、=$a2=2196 （绿色）等）并将A2每个应用于整行。

SU528202 示例

Question 2

以下 Excel VBA 例程和支持函数提供了根据数据范围第一列中的数字组突出显示多行的功能，此功能只能通过条件格式来实现。可以选择任意数量的列和行，但我尚未测试大型表格的性能。

代码很简单，循环遍历选定范围内的单元格，当程序沿着范围向下移动时，第一列中的值发生变化时应用新的颜色。

颜色选择方案非常简单。根据程序中设置的不同颜色数量（当前为 16 种），选择 Excel（2007 及以上版本）支持的色谱中等距的颜色，然后随机分配给数据表中的行分组。

对于深色，单元格中的数字或文本设置为白色以形成对比。

这两个支持函数向主程序提供填充颜色和字体颜色代码。

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

Answer