Conditional Formatting to alert a change from an adjacent cell

[David Evans]

Greetings Ninjas!

I have a fee table that I'd like to format for when a value in a column changes from the cell above it ..... The reason i want to automate it is that its possible that this table will get larger and be changed and it would be helpful to see the fee tiers as colors for each rate ....

Is this a Conditional Format or do I need to investigate a VBA solution?

As usual, suggestions are most gratefully appreciated

D

 

[Luke M]

Ok if we use the CF color scale option? I went with default red to green, but you can pick your own colors. Made it pretty easy to see the changes, and took me all of 5 secs.

 

[David Evans]

Thanks Luke -its definitely an improvement on what I had

I'll go and see if I can make the colors a bit more distinct.

BTW, you're slowing down - 5 seconds?!

 

[David Evans]

Currently I've got 31 unique values in this table - I'm pondering creating a list of the unique values and assigning each a "Color" and then formatting the values based on that .... does that make sense to anyone here. The color gradation in the Color Scale option doesn't differentiate those values thatare close to each other sufficiently ...

 

[David Evans]

This is the sort of thing I'm looking to do, either via VBA or Conditional Formatting ... Whenever a value changes in a column, there's a dramatic change in the color

 

[Luke M]

Hi David.
We can still use CF I believe. We'll use the formula to count unique values, from MS's site
http://office.microsoft.com/en-us/e...nique-values-in-a-data-range-HP003056118.aspx

Attached used a single CF across whole range, but you could certainly do it by column and then give different colors if you want.

 

[David Evans]

Great Stuff Luke - that is exactly what I was looking for - greatly appreciate your effort on this. Your Ninja Status is wholly deserved! I bow to your knowledge!