Conditionally Color Blocks of Cells

[BrianWren]

Given data like:

   A   1   Z
   C   1   Y
   L   1   X
   R   2   W
   S   2   V
   T   3   U

How can I conditionally color blocks based upon having the same contiguous value in the 2nd column?
Alternating is fine.

In the case above:

The 1st 3 rows would be one color, 3 columns wide
This would change to a different color for rows 4 and 5.
Finally, there would be another transition, moving into the 6th row.

 

[Joe4]

Welcome to the Board!

In situations like this, I will often use Conditionally Formatting to format the even numbers different from the odd numbers, so get alternating colors as the value in your second column changes.

Let's say your data is in range A1:C6.
First, highlight your whole data range.
Then go into Conditional Formatting and select the formula option.
Enter the following formula: =ISEVEN($B1)
Then, select your color option.

You can then repeat the same steps and add a second condition, checking for ISODD and selecting a different color (or I often only do one, as it will just alternate between color and no color).

 

[BrianWren]

The example I gave used numbers, just as a visual aid to see the task.
The values are actually names.
It is the transition from one repeated name to a new repeated name that I am trying to use as the cue to alter the shading.

 

[Joe4]

You have learned a very important lesson here, why you do not want to oversimplify your question. Otherwise, you might get an answer that solves the question you asked, but does not solve the problem you actually have. It is best to ask your question, with the actual details (and examples) of your situation. That increases the likelihood of getting an answer that will actually work for you.

One way to get my previous suggestion to work for you is to create a "helper" column that works as a counter that changes every time your value in column B changes.
So, if we go back to the example, we would put our helper column in column D.
In D1, enter 1.
Then in D2, enter the following formula and copy down for all rows: =IF(B2=B1,C1,C1+1)

Then, if you use column D instead of column B in your Conditional Formatting formula, it should do the alternating color.

 

[BrianWren]

You have learned a very important lesson here, why you do not want to skim questions. Otherwise you might miss what the questioner actually asked.

I explicitly said "... based upon having the same contiguous value in the 2nd column.

I had tried setting D to =(B[current-row] = B[current-row-1]), (just selecting the cells to fill in the formula, of course, so it looks more like =(B3=B2)), yielding:

   A   1   Z   FALSE
   C   1   Y   TRUE
   L   1   X   TRUE
   R   2   W   FALSE
   S   2   V   TRUE
   T   3   U   FALSE

That didn't help me much. But I think incrementing a number based on the same test, and then testing for odd/even will be successful.

Thanks.

 

[BrianWren]

That did the trick!

Thanks, again, Joe4.

 

[Joe4]

You have learned a very important lesson here, why you do not want to skim questions. Otherwise you might miss what the questioner actually asked.

I explicitly said "... based upon having the same contiguous value in the 2nd column.

I had tried setting D to =(B[current-row] = B[current-row-1]), (just selecting the cells to fill in the formula, of course, so it looks more like =(B3=B2)), yielding:

No, I understood your question perfectly clear, but unfortunately, I made a small typo in my formula (referencing the wrong column).
It should have been:
=IF(B2=B1,D1,D1+1)
instead of
=IF(B2=B1,C1,C1+1)

Sorry about that...

By the way, my comments were not meant to be a critique, but rather a tip on how to get the best possible help, to get an answer that will actually work for your situation. Many times, a person will ask an oversimplified question, when there actual question is more complex. Then, the often get an answer to the question asked, and come back and say that there real problem is more complex than that, then disclose those other details. And often times, they do not get a response to their follow-up question. So both the questioner and answerer go away frustrated, and the actual problem goes unsolved.

So just trying to help you avoid that frustration in the future, and get the most of what you can from our board.