Color rows based on Value above

[jomili]

I'm sorry; I'm down with some type of sinus sickness, so my brain isn't working right, otherwise I'm sure I could figure this out. I want to color each row in my spreadsheet if the value in column C matches the value of column C above or below it. I know how to color the rows, but I'm drawing a blank on how I'd set up the

Please Login or Register  to view this content.

We're talking about 14000 lines, and in that range there are 6000 unique values.

Thanks in advance for any help on this.

[Richard Buttrey]

Hi,

Just use a conditional format with the following 'Formula Is' option

Please Login or Register  to view this content.

and apply it to however many cells/rows you want, e.g. $A$2:$Z$100

[jomili]

That doesn't quite get it. It's highlighting the duplicate items, but it's also highlighting one row above those duplicates and one row below. See the example attached.

[jomili]

I found a VBA approach, but need some help refining it. Here's how it exists so far:

Please Login or Register  to view this content.

My problem on this is that the macro was originally written to highlight only the cell, but I want it to highlight the row from the first column to the last column used, hence I have those variables in place, but don't know how to use them to replace the line:

Please Login or Register  to view this content.

[Richard Buttrey]

Hi,

Forget the first And condition, I didn't know whether you had blanks in your rows.

Use instead the cond. format

Please Login or Register  to view this content.

[jomili]

Richard,

It's still not working correctly. See the picture.

[NBVC]

Richard's solution works.

Delete all the CFs you have in the range and start fresh.

Select from A2:C105 and apply formula:

=OR($C1=$C2,$C3=$C2)

[romperstomper]

You added it incorrectly (the formula assumed you had a cell in row 2 active when you did it) so the rows being compared were off by one. See attached.

[jomili]

Okay, so it seems I need to select my used range not counting the header range and apply the formula to that to highlight all of my data on the rows with duplicate data in column C. So, to set it up via macro I guess I'd do this:

Please Login or Register  to view this content.

do you see anything else I'd need to add to my code to make this a reusable macro?

[romperstomper]

The formula would be wrong if the usedrange does not start in row 1

[jomili]

So if the used range started on Row 3, the formula would need to be

Please Login or Register  to view this content.

, right?

How would I do it to make the formula variable depending on what the first row is?

[romperstomper]

Something like this, using R1C1 format:

Please Login or Register  to view this content.

[jomili]

Thanks for catching that. Here's my updated macro in case anyone else can benefit from it.

Please Login or Register  to view this content.

[jomili]

Changed it to do away with the selection. Also refigured the used range (I was getting colors in deleted columns).

Please Login or Register  to view this content.