Conditional formatting based on other cell values?

[forteanajones]

I am working with a table and have a special formatting requirement. Ordinarily I would use conditional formatting to color-code cells as I need to but in this case, certain cells need to be colored in certain ways depending on the contents of other cells. More specifically, when a series of numbers has ended and a new series has begun.

Example:

Book1
	A	B	C	D
1	1	Duis pharetra
2	2	Morbi consectetuer
3	3	Phasellus euismod
4	2	Morbi consectetuer
5	3	Phasellus euismod
6	4	Aliquam ac sem
7	3	Phasellus euismod
8	4	Aliquam ac sem
9	5	Nullam iaculis augue
10	4	Aliquam ac sem
11	5	Nullam iaculis augue
12	6	Vivamus auctor eros
Sheet1

(Note: The above uses a series of three for the sake of simplicity here in this discussion thread. In my actual spreadsheet I am using series of ten instead. Also, each given number in column A will always determine the exact text that appears in column B.)

I fear the only way to deal with this is to use a VBA solution, but I need to import my spreadsheet into another application (Xcelsius) which ignores VBA completely.

Are there any creative possibilities to produce this effect without relying on VBA?

And assuming VBA is indeed the only viable option, can anyone here help me out with the code?

 

[DLEET]

try conditional formatting based on a "Formula" rather than the cell value.

this is an option on the conditional formatting menu

 

[jim may]

Looks to me that if a number in Column A is ever LESS-THAN the
number in the row just before it, then you want to Switch colors. Right?

It must be a Coincidence that in your example it does so every 3 rows.. Right?

 

[forteanajones]

try conditional formatting based on a "Formula" rather than the cell value.

this is an option on the conditional formatting menu

[sheepish]Honestly, I may not have ever quite grasped the full capabilities of the "Formula" option in conditional formatting so it looks like I really should dive into the Help docs again. I had a heck of a time grasping the little document I read before, and may have misunderstood the limitations. Sounds like that may do the trick.[/sheepish]

Looks to me that if a number in Column A is ever LESS-THAN the number in the row just before it, then you want to Switch colors. Right?

It must be a Coincidence that in your example it does so every 3 rows.. Right?

This is correct, as soon as a numeric series changes (e.g. starts over at some other number) the color changes. Doesn't really matter to me what the color is but I would like to alternate between two colors. And it's not really a coincidence that the series is exactly three rows (or in my actual spreadsheet here, ten rows per series). That's pretty much always the case.

 

[forteanajones]

I gave conditional formatting another shot and used =MOD(ROW(),1)=0 to render series a specific cell and font color, but it seems that this won't work in my situation given the variability of which row a series might actually begin.

For example with the chart I pasted at the top of this thread, the very first row of that table might or might not begin with a "1", or any specific number. It really depends on the user input, and each series could start at any number between 1-22.

Once it begins, however, it sticks to the pattern I described. The constant factors are:

1. Each numeric series is ten rows long (there are very rare cases where this may be different, but I'll deal with those possible exceptions later)
2. Each individual number in the A column will correspond with a specific string of text in the B column (B uses VLOOKUP).
3. The color will always switch for both A and B in a given row, when the number in A is less than the number in the A immediately above it.

 

[forteanajones]

Any takers? Tried this again with a more sensible formula but I'm not sure conditional formatting is going to do the trick.

 

[DLEET]

in your conditional format formula - try something like:

=$A10<$A11

this would be placed in the conditional format formula for cell A11

once this is working, paste the format into cell B11

if you are getting hte desired results - you can paste the format to the rest of the affected rows.

I do see some issues with repeating colors though - since you are only allowed 3 conditional formats. you may need to do some VB programming