[excel] Excel - Shading entire row based on change of value

I would like to shade entire rows in Excel based on the value of one cell. For example say I have the rows below:

**File No**
1122
1122
1144
1155
1155
1155
1166

I would like the first two rows (where the value of the file # is 1122) to be shaded in color 1, the next row (where the value of the file # is 1144) to be shaded in color 2, the next 3 rows (where the value of the file # is 1155) to be shaded color 1, the next row (where the value of the file # is 1166) to be shaded color 2

What you can do is create a new column over on the right side of your spreadsheet that you'll use to compute a value you can base your shading on.

Let's say your new column is column D, and the value you want to look at is in column A starting in row 2.

In cell D2 put: =MOD(IF(ROW()=2,0,IF(A2=A1,D1, D1+1)), 2)

Fill that down as far as you need, (then hide the column if you want).

Now highlight your entire data set - this selection of cells will be the ones that get shaded in the next step.

From the Home tab, click Conditional Formatting, then New Rule.

Select Use a formula to determine which cells to format.

In "Format values where this formula is true" put =$D2=1

Click the Format button, click the Fill tab, then choose the color you want to shade with.

Examples here:

Use Conditional Formatting.

In it's simplest form, you are saying "for this cell, if it's value is X, then apply format foo". However, if you use the "formula" method, you can select the whole row, enter the formula and associated format, then use copy and paste (formats only) for the rest of the table.

You're limited to only 3 rules in Excel 2003 or older so you might want to define a pattern for the colours rather than using raw values. Something like this should work though:

I have found a simple solution to banding by content at Pearson Software Consulting: Let's say the header is from A1 to B1, table data is from A2 to B5, the controling cell is in the A column

1. Make a new column, C
2. At first the first row to color make the formula =true in the C2 cell
3. In the second row make the formula =IF(A3=A2,C2,NOT(C2))
4. Fill the column down to the last row
5. Select the data range
6. Select conditional formatting, choose Use a formula... and put =$C2 as the formula

I had to do something similar for my users, with a small variant that they want to have a running number grouping the similar items. Thought I'd share it here.

• Make a new column A
• Assuming the first row of data is in row 2 (row 1 being header), put 1 in A2
• Assuming your File No is in column B, in the second row (in this case A3) make the formula =IF(B3=B2,A2,A2+1)
• Fill/copy-paste cell A3 down the column to the last row (be careful not to copy A2 by accident; that will populate all cells with 1)
• Select the data range
• In the Home ribbon select Conditional Formatting -> New Rule
• Choose Use a formula to determine which cells to format
• In the formula cell, put =MOD($A1, 2)=1 as the formula
• Click Format, select the Fill tab
• Select the Background Color you want, then click OK
• Click OK

In MS Excel, first save your workbook as a Macro Enabled file then go to the Developper Tab and click on Visual Basic. Copy and paste this code in the "ThisWorkbook" Excel Objects. Replace the 2 values of G = and C= by the number of the column containing the values being referenced.

In your case, if the number of the column named "File No" is the first column (namely column 1), replace G=6 by G=1 and C=6 by C-1. Finally click on Macro, Select and Run it. Voila! Works like a charm.

Sub color()
    Dim g As Long
    Dim c As Integer
    Dim colorIt As Boolean

    g = 6
    c = 6
    colorIt = True

    Do While Cells(g, c) <> ""
        test_value = Cells(g, c)
        Do While Cells(g, c) = test_value
            If colorIt Then
                Cells(g, c).EntireRow.Select
                Selection.Interior.ColorIndex = 15
            Else
                Cells(g, c).EntireRow.Select
                Selection.Interior.ColorIndex = x1None
            End If
            g = g + 1
        Loop
        colorIt = Not (colorIt)
    Loop
End Sub

If you are using MS Excel 2007, you could use the conditional formatting on the Home tab as shown in the screenshot below. You could either use the color scales default option as I have done here or you can go ahead and create a new rule based on your data set.

This one has puzzled me for ages. Don't like the idea of creating an extra (irrelevant) row/column just to calculate formatting. Finally came up with the following rule:

=INDIRECT("A"&ROW())<>INDIRECT("A"&(ROW()-1))

This creates the reference A2<>A1 for row 2, A3<>A2 for row 3 etc. Adjust the letter "A" to be the column you wish to compare

A simpler version of one of the above answers. Column A is the key.
Yes, it needs a helper column.  That's column K.
1) Set first cell in table to TRUE (K8)
2) On second row at K9, and to end of table (K99), paste: =IF(A8=A9,K8,NOT(K8))
This gives a pattern of TRUE...TRUE, FALSE...FALSE,...
3) Select key column A1:A99, or whole table A1:K99
4) Set Home/Conditional/New Rule/Formula  =K8
5) Format as you wish for the TRUE cells
6) Select the range from (3) and Right-click Format White (or whatever
background color you want) for the FALSE cells

Note that this solution (and the others) have a major flaw, in that this
highlighting doesn't work properly when you have filters active in your
table.  I want that fix :)

you could use this formular to do the job -> get the CellValue for the specific row by typing: = indirect("$A&Cell()) depending on which column you have to check, you have to change the $A

For Example -> You could use a customized VBA Function in the Background:

Public Function IstDatum(Zelle) As Boolean IstDatum = False If IsDate(Zelle) Then IstDatum = True End Function

I need it to check for a date-entry in column A:

=IstDatum(INDIREKT("$A"&ZEILE()))

have a look at the picture:

Like at least 1 other contributor here, I also have never liked having to add a extra "helper" column, which can create some hassles in various situations. I finally found a solution. There are a couple different formulas that you can use depending on needs and what is in the column, whether there are blank values, etc. For most of my needs, I have landed on using the following simple formula for the Conditional Formatting (CF) formula:

=MOD(Fixed(SUMPRODUCT(1/COUNTIF(CurrentRange,CurrentRange))),2)=0

I create a Named Range called "CurrentRange" using the following formula where [Sheet] is the sheet on which your data results, [DC] is the column with the values on which you want to band your data and [FR] is the first row that the data is in:

=[Sheet]!$[DC]$[FR]:INDIRECT("$[DC]$" & ROW())

The sheet reference and column reference will be based on the column that has the values you are evaluating. NOTE: You have to use a named range in the formula because will throw an error if you try to use range references directly in the CF rule formula.

Basically, the formula works by evaluating for each row the count of all of the unique values for that row and above to the top of your range. That value for each row essentially provides an ascending Unique ID for each new unique value. Then it uses that value in the place of the Row() function within the standard CF MOD function formula for simple alternating row colors (i.e. =Mod(Row(),2)=0).

See the following example that breaks down the formula to show the resulting components in columns to show what it is doing behind the scenes.

In this example, the CurrentRange named range is defined as:

=Sheet1$A$2:INDIRECT("$A$" & ROW())

The Unique ID column contains the following portion of the CF formula:

=Fixed(SUMPRODUCT(1/COUNTIF(CurrentRange,CurrentRange)))

You can see that, as of row 3, the count of unique values from that row and above in the "Color" column is 2 and it remains 2 in each subsequent row until row 6 when the formula finally encounters a 3rd unique value.

The Band column uses the remainder of the formula referring to the result in column B =MOD(B2,2) to show how it gets you to the 1s and 0s that can then be used for CF.

In the end, the point is, you don't need the extra columns. The entire formula can be used in the CF rule + a named range. For me, that means I can stick the basic formula in a template that I use to drop data in and not have to worry about messing with an extra column once the data is dropped in. It just works by default. Additionally, if you need to account for blanks or other complexities or large sets of data you can use other more complex formulas using frequency and match functions.

Hope this helps someone else avoid the frustration I have had for years!

I hate using these in-cell formulas and having to fill in a new column, and I finally learned enough to make by own VBA macro to accomplish this effect.

This might not be all that logically different from another answer, but I think the code looks a hell of a lot better:

Dim Switch As Boolean
For Each Cell In Range("B2:B" & ActiveSheet.UsedRange.Rows.Count)
    If Not Cell.Value = Cell.Offset(-1, 0).Value Then Switch = Not (Switch)
    If Switch Then Range("A" & Cell.Row & ":" & Chr(ActiveSheet.UsedRange.Columns.Count + 64) & Cell.Row).Interior.Pattern = xlNone
    If Not Switch Then Range("A" & Cell.Row & ":" & Chr(ActiveSheet.UsedRange.Columns.Count + 64) & Cell.Row).Interior.Color = 14869218
Next

My code here is going by column B, it assumes a header row so it starts at 2, and I use the Chr(x+64) method to get column letters (which won't work past column Z; I haven't yet found a simple-enough method for getting past this).

First, the boolean variable will alternate whenever the value changes to a new one (uses Offset to check cell above), and for each pass the row is checked for either True or False and colors it accordingly.