Conditional formatting based on other cell values?

forteanajones

New Member
Joined
Aug 28, 2005
Messages
41
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
ABCD
11Duis pharetra
22Morbi consectetuer
33Phasellus euismod
42Morbi consectetuer
53Phasellus euismod
64Aliquam ac sem
73Phasellus euismod
84Aliquam ac sem
95Nullam iaculis augue
104Aliquam ac sem
115Nullam iaculis augue
126Vivamus 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?
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

DLEET

Board Regular
Joined
Nov 25, 2005
Messages
95
try conditional formatting based on a "Formula" rather than the cell value.

this is an option on the conditional formatting menu
 

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,484
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

New Member
Joined
Aug 28, 2005
Messages
41
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

New Member
Joined
Aug 28, 2005
Messages
41

ADVERTISEMENT

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

New Member
Joined
Aug 28, 2005
Messages
41
Any takers? Tried this again with a more sensible formula but I'm not sure conditional formatting is going to do the trick.
 

DLEET

Board Regular
Joined
Nov 25, 2005
Messages
95
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,387
Messages
5,624,389
Members
416,026
Latest member
melvic69

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top