excel - highlight by value, alternating highlights

Madpanda

I wasn't able to figure this out after 20 mins of googling so here we go

I have a column which looks like this

apples
apples
apples
oranges
pears
pears
pears
pears
kiwi
kiwi


I'd like to either via conditional formatting or a macro highlight the column or the whole row based on that column value, but with alternating or an array of highlight options for readability.

So apples would be highlighted green, oranges blue, pears yellow etc.

Just highlighting duplicates would work for this also but I haven't figured out how to use different highlights for each result. The sheet is large enough that they are probably 30+ key values (apples etc).

Cog

This what you're looking for?



"Equal To" will require an exact match, "Text That Contains" will just need the text within the cell, but the cell can have more than just that in it.

When you're specifying what the field is equal to or contains, you can click one of the cells to have it source the cell ID.



If the default formats are not pleasing (they suck) you can set the font and background options as you wish by choosing Custom Format.

Did I totally miss what you were after?

Madpanda

That would work if i want to create rules for each of the 30+ (and thats just for this month) keys (apples,oranges etc).

I could just as easily manually do it.

Looking for something that will automatically highlight all cells that contain instances of a value.

So it would go through column d, see that the first item is apples, and color every cell in column d with the text apples in a certain color. Then continue and run into oranges, coloring those cells a different color.

They are already sorted by Column D (the keys are actually numeric) the problem lies in distinguishing lines next to each other, ie 12345 and 12354.

Daenris

Do you actually need them colored, or are you just doing that to distinguish where the values change? Because, if you just need to see where the values change, I'd create a new column, and starting on row 2 do a formula like =D2-D1 then copy down the column. That way, any row in which the value is different from the previous row will show up with a non-zero value there.

Madpanda

That would kind of work if the columns weren't filtered so row numbering isn't sequential.

Artereis

Why not some form of pivot table/chart?