Highlighting one or two numbers in a column based on a value in another column

Hello, I am a novice Excel user (but learning!). I am new here but have found some great information and have almost solved my problem on my own.

I want to have Excel highlight one or two numbers/cells in one column based on the value in another cell. I am attempting to do this using Conditional Formatting.

For example, if X1 is BETWEEN A2 and A3, I want to highlight A2 AND A3. I have used various AND and IF functions to achieve this goal.
Also, if X1 is EQUAL to A2, I want A2 to be highlighted. This is easy to do with a simple formula.

But my problem is this: if X1 is equal to A2, I want ONLY A2 highlighted. If X1 is between A2 and A3, I want BOTH A2 and A3 highlighted. I cannot figure out how to achieve both. For example, when X1 = A3, A2 AND A3 are being highlighted, instead of just A3. I would prefer to do this without a macro, if possible. Can this be done?

Example formulas I have tried which easily achieve the highlighting of two cells when the value is between them:

Condition 1=IF(AND($I$35>I8,$I$35<I7),1," ")+IF($I$35<>I7,1," ")
Condition 2=IF(AND($I$35<I6,$I$35>I8),1," ")+IF($I$35<>I7,1," ")

It appears that the +IF, etc. functions are not doing anything.

This is more complicated by the fact that the value of, in the above example, I35 is based on a value from another sheet (I used a function to transfer a value from one sheet to I35 in the other sheet). As data are entered on Sheet1, the value in I35 in Sheet2 changes.

Hopefully this all makes sense. Thank you for any help.

I would think this one CF formula is all you need:

=OR(AND($I6<=$I$35,$I7>=$I$35),AND($I7<=$I$35,$I8>=$I$35))


CF formulas don't need IF statements, by design they should resolve to simple TRUE/FALSE results.

Thank you for your help! That formula looks promising but it did not work. Perhaps I need to provide more information, or I may be doing something wrong.

I want the formula to act on the values in the cells of the column I, I7 through I32. The other value is I35. In my example I used "$I$35" to keep I35 absolute, so that I could select the entire I column (7 through 32), enter conditional formatting, and enter a single function for all cells (because Excel would automatically change the other cell designations). I tried your formula with and without the "$" symbols (except for in reference to I35), but nothing is being highlighted.

To make it more clear, column I looks like this (the first column here is just to designate cell numbers):

I
7 4.92
8 4.08
9 3.78
10 3.58
11 3.36
12 3.27
13 3.18
14 3.09
15 3.00
16 2.91
17 2.83
18 2.78
19 2.73
20 2.70
21 2.67
22 2.63
23 2.58
24 2.55
25 2.52
26 2.50
27 2.46
28 2.42
29 2.39
30 2.35
31 2.31
32 2.27

Some examples of what I want to happen: if the value in I35 is 3.36, then cell I11 (containing 3.36) should highlight, and only this one should highlight. If the value in I35 is, say, 2.40, then I want I29 as well as I28 to highlight. I can only get it to highlight two cells, even when I35 equals one of the values above. Does that make sense?

Also, I tried revisions of the formula you gave.

If I use

=OR(AND(I6>=$I$35,I7<=$I$35),AND($I7>=$I$35,$I8<=$I$35),I7=$I$35)

It will highlight two numbers even when I35 is equal to a value in one of the other cells. In other words, if I35 is 3.36, it highlights 3.36 AND 3.58.

If I use

=OR(AND(I6>=$I$35,I7<=$I$35),I7=$I$35)

It will highlight only one number, regardless of the value in I35. For example, when I35 is 3.55, it correctly highlights 3.36, but does NOT highlight 3.58.

This has me stumped. I figured there HAD to be a way to do what I want, but I cannot seem to get Excel to do BOTH things. It either always highlights one, or always highlights two.

Ok, first, one normally expects an ordered table like that to ASCEND, not DESCEND, so the operators are backwards.

Second, you will need two CF formulas if you only want one cell to light up when it's an exact match, but two to light up if it's an inbetweener.

CF1: =$I7=$I$35 (you must set this rule to "STOP IF TRUE")

CF2: =OR(AND($I6>$I$35,$I7<$I$35),AND($I7>$I$35,$I8<$I$35))

http://screencast.com/t/tff0Cny0N

I dont believe 2003 has the "stop" option

In Excel 2003 you can put up to 3 CF rules on a cell, in the order of importance. If any rule resolves to TRUE, it stops by design. You don't have a choice.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Thanks.

Post a usable workbook demonstrating all these problems/needs.

Make sure there is just enough data to make it clear what is needed. Include BEFORE and AFTER sheets if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

I probably should have uploaded something to begin with.

The file is a sample file with none of the conditional formatting inserted. I deleted all irrelevant data. On the "client data" sheet, there are white boxes surrounded by yellow areas. These white boxes are where data are entered. The sums and means in the yellow areas (rows 18 and 19) are then automatically calculated. On the "percentages" sheet, Row 35, columns C through M, contain numbers calculated in the "client data" sheet. Rows 7 through 32 in columns C through M contain data which must remain as entered (based on data from an outside source). What I want is for the values in the C through M columns to be highlighted based on the values in row 35. If a value in column C exactly matches C35, then that value should be highlighted. If the value in C35 is between two values in column C, I want both highlighted. In other words, if C35 = 3.03, I want C15 (3.03) highlighted, and ONLY C15 (for that column). If C35 = 3.00, I want BOTH C15 (3.03) and C16 (2.97) highlighted. This applies to each column.

Notice that when data are entered into the boxes in the "client data" sheet, the numbers in sheet "percentages," row 35 change, but these are the only numbers that change in the "percentages" column.

Preferably, I would like the highlighted cells to be highlighted as such: exact match, single cell = pattern Bright Green, font face Bold, text color black; inbetween, two cells = pattern Green, font face bold, text color white.

Hopefully this all makes sense.

Finally, I am not averse to the idea of having both sheets on a single sheet, and I can re-format the "percentages" sheet if it needs to be moved. As long as the numbers are highlighted, that's all that matters!

Thanks again!

I think you can fix this yourself with the solutions given already. The problem is the values in row35 look like one thing, but are another.

The value C35 is showing as 3.77, but the value is actually 3.770391414

If you correct that so it really can be evaluated as only a two-decimal value, the other CF formulas provided earlier can work, and you can amend the coloring as well to the scheme you've mentioned.

So, the formula in C35 that "fixes" the value in C35 to only two decimals would be:

C35: =ROUND('Client Data'!U25, 2)



Keep this in mind in the future. Values displayed as the result of a formula that divides may need to be rounded before additional math is done to eliminate unwanted values past the decimals allowed.

Glad to hear. Don't beat yourself up over the rounding thing, it is not obvious until it bites you one time real hard, as it has done to all of us.