CODEWITHSUNDEEP
excelconditional-formatting
davedave1919
davedave1919

Reputation: 123

Conditional Format in Excel when adjacent cells are equal

I have numbers stored in cells D5:S19. Each row of this array represents a specific product, the number in each column represents the cost of this item in a given financial quarter (if the product is not available in a given quarter then the cell is blank):

enter image description here

I'm struggling with setting up a conditional format which will highlight the cells which are adjacent and equal on a row by row basis (excluding the blank cells). I have tried using a formula based around the EXACT function (EXACT($D5,$E5)) for the entire array but this doesn't seem to work at all.

Any pointers here would be greatly appreciated. Thank you.

Upvotes: 1

Views: 1180

Answers (1)

asongtoruin
asongtoruin

Reputation: 10359

The way I see it, your highlighted data is going to be in one of three states. It is either:

  1. The first entry of several consecutive entries
  2. The last entry of several consecutive entries
  3. Somewhere in the middle.

This lends itself to three distinct conditional formatting rules, to make sure that the use can distinguish between different sets of consecutive values. These come through as follows:

  1. Rule 1

  2. Rule 2

  3. Rule 3

Notice the borders specified for each rule. We then order the rules in reverse order, and set rule 3 to "Stop if True". This means that left and right borders aren't added to our middle cells, like so:

Rule ordering

This results in the following conditional formatting (I have only used a subset of your data)

Conditional formatting

Here's a text version of each of the rules for you to copy:

=AND(D5<>"",D5=E5)
=AND(D5<>"",D5=C5)
=AND(D5<>"",D5=E5,D5=C5)

Hope this helps!

Upvotes: 1

Related Questions