On 25 Aug., Mike wrote: > Thanks in advance! > 1. I am using MS Excell 2007 > 2. Problem: I have a Column of serial #'s and they are listed low to > high. Some of the Serial # are listed once, some twice and some 3 > times. > 3. I want to alternate shading for each set of the same serial #'s. > > EX: > 276CHK0130 > 276CHK0130 > > 276CHK0131 > 276CHK0131 > 276CHK0131 > > 276CHK0132 > 276CHK0132 > 276CHK0132 > > 276CHK0133 > > Does anyone know how this can be done? Is there a formula I can use > for CONDITIONAL FORMATTING?
I put together some VBA code that might fit your problem (note that you'll have to install the Excel's VBA editor from the installation disk first, see http://www.excel-vba.com/excel-vba-contents.htm#VBA). Copy the following code into the Code Module that belongs to the workbook. Then you can run this macro by pressing Alt+F8 (that works at least under Excel 2003, I cannot test it with 2007 :-( Before you run the macro, you should select the cell you want to shade. I made the following assumptions: (A) The serial number is in the first column of the selection, (B) the sequential number should be put into the column left of the selected range, (C) the selection contains no empty rows (as your posting suggests). If any of these assumptions are wrong, you can easily fix the code so that it fits your needs. Public Sub ShadeAlternateRows() ' If the user has not selected anything, we ' just leave. Feel free to add some message box. If Selection Is Nothing Then Exit Sub End If ' Show a dialog that lets the user decide which color ' should be used for the shading. Dim colorSelector As MSComDlg.CommonDialog Set colorSelector = New MSComDlg.CommonDialog colorSelector.ShowColor Dim CurrentSelection As Excel.Range Set CurrentSelection = Selection Debug.Assert Not CurrentSelection Is Nothing ' remove any previous shading CurrentSelection.Interior.ColorIndex = xlColorIndexNone ' Iterate through the rows and shade all blocks with the ' same key in the same color: We assume that the key value ' is in the first column of the selection. Dim CurrentRow As Long Dim LastKey As String Dim LastColor As Variant Dim LastBlockNumber As Long LastColor = 0 LastBlockNumber = 0 For r = 1 To Selection.Rows.Count If LastKey = CurrentSelection.Cells(r, 1).Value2 Then ' If the key is the same, we can use the color of the last line. Selection.Rows(r).Interior.Color = Selection.Rows(r - 1).Interior.Color Else ' Change the color. If LastColor = 16777215 Then LastColor = colorSelector.Color Else LastColor = 16777215 End If ' Increase the block number. LastBlockNumber = LastBlockNumber + 1 ' Use the changed color for the current row. Selection.Rows(r).Interior.Color = LastColor ' Remember the current key for the next iteration of this loop. LastKey = CurrentSelection.Cells(r, 1).Value2 End If Selection.Cells(r, 0).Value2 = LastBlockNumber Next r End Sub Regards, Stuart -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
