Excel - Shading entire row based on change of value

Question

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

User · Answer

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

enter image description here

User · Answer

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  amp ROW    lt  gt INDIRECT  A  amp  ROW  -1     This creates the reference A2 lt  gt A1 for row 2  A3 lt  gt A2 for row 3 etc  Adjust the letter  A  to be the column you wish to compare

User · Answer

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

User · Answer

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

User · Answer

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     amp  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    amp  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

User · Answer

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   Make a new column  C At first the first row to color make the formula  true in the C2 cell In the second row make the formula  IF A3 A2 C2 NOT C2   Fill the column down to the last row Select the data range Select conditional formatting  choose Use a formula    and put   C2 as the formula

User · Answer

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

User · Answer

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

User · Answer

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   lt  gt             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

User · Answer

you could use this formular to do the job -  get the CellValue for the specific row by typing       indirect   A amp 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  amp ZEILE      have a look at the picture

User · Answer

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   amp  ActiveSheet UsedRange Rows Count      If Not Cell Value   Cell Offset -1  0  Value Then Switch   Not  Switch      If Switch Then Range  A   amp  Cell Row  amp       amp  Chr ActiveSheet UsedRange Columns Count   64   amp  Cell Row  Interior Pattern   xlNone     If Not Switch Then Range  A   amp  Cell Row  amp       amp  Chr ActiveSheet UsedRange Columns Count   64   amp  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

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

Examples related to excel