Excel 2007 - Conditional Formatting Nightmare.

[thewigglesworth]

Ok here's where I am at this point:

I have an excel workbook that has a static "map" where cells represent physical locations in a store. Each cell on that map has a location code entered as its value. I'm attempting to get the static location code cells on the map to change color based on the sales numbers associated with that location. I've attached a workbook that gives a pretty accurate depiction of the type of interface we're talking about.

Here's where I'm stuck. I can't figure out how to get conditional formatting to identify that if any cell in a range (my example workbook is A1:C7) matches the value of any cell in H (attached example) the sales data from the corresponding cell in I will dictate the color shading based on distance from the AVG of column I. I've used a Pivot Table to pull the data from another spreadsheet in the file I'm working on, however I'm just interested in getting this to work period.

Any help (or guidance) will be greatly appreciated. It's annoying because I can conceptualize what I need to do, just not HOW to do it.

Static Locations.xlsx

[humdingaling]

i think i understand what you are asking for

see attached file

i have used conditional format formula

Formula below

Please Login or Register  to view this content.

applies to
=$A$1:$C$7

i did a second one which applies to same area but slight change in formula

Please Login or Register  to view this content.

[thewigglesworth]

I want to mention that I'm not personally in love with being forced to use 2007 for this, and if it's easier to solve in later versions that would be good info (a reference would help as well) since that might allow me to negotiate switching the versions company-wide.

[thewigglesworth]

Thanks for getting back to me so quickly!

The sheet I just downloaded from you didn't quite solve the problem I'm talking about, and also appears to have overwritten some cells... I've redone the colors so you can see more clearly what I'm talking about having happen. The goal is for virtually the EXACT same color coding from column "I" applied to the cells on the far left of the sheet (A1:C7) without changing the text on that block of cells.Static Locations.xlsx

[FDibbins]

As long as all references are on the same sheet, 2007 CF works well, but it cannot reference (directly) other sheets. You need to create range names and use that as references in the CF rules. 2010-onwards overcomes that
restriction

Also, CF cannot reference colors, colors are cosmetic, and CF (and other formulas) works on data

[thewigglesworth]

As long as all references are on the same sheet, 2007 CF works well, but it cannot reference (directly) other sheets. You need to create range names and use that as references in the CF rules. 2010-onwards overcomes that
restriction

Also, CF cannot reference colors, colors are cosmetic, and CF (and other formulas) works on data

Ok thanks for that info! My hang up is that I know what to do just not how. The sentence in my head for the conditional formatting is:

If a cell between A1 To C7 equals the value of a cell in column "H", color the cell (from A1:C7) based on how the value in the column immediately right from the cell in "H" corresponds to its position relative to the running Avg of that column (in this case I).

So if A1 = H1, then A1 would be highlighted based on the value of I1 because that's the corresponding sales data.

[humdingaling]

ok
since you posted in VBA section i assume a VBA solution would work
otherwise i am at a loss

im pretty sure this works in excel 2007

Please Login or Register  to view this content.

[FDibbins]

OP posted in CS forum, so this thread is now closed