Conditional Formating - Dynamic "not between"

[Kathy Belvel]

I have a table (tblTeeSelectionAnalysis) containing 8 columns and 60 data rows. Each row is the data for a given golfer. Three (3) of the table columns (Low, Actual and High) contain the following information for each player's record as follows;

Low and High contain the low and high handicap Index (HI) values forming the recommended HI range for the recommended tee (white, red, gold, etc.) for the player based on her actual HI, which is contained in the “Actual” column of the table.

Example:
B C D E F G H I
2 Name Declared Recommended Status Low Actual High Notes
3 kathy R/W Combo White Check 20 17.8 26.9 Check selection

Based on the above "Actual" HI value, 17.8, the player should be playing form the White Tees not the declared R/W Combo tees. Setting the Conditional Formatting rule for the Current Selection ($G$3) as follows;

Cell Value not between $F$3 and $H$3
If condition is true, format font color = red

This works great for this one row (row 3). If I copy this cell down to the end of the table (row 62) the between cell references remain $F$3 and $H$3. So, the font highlight is incorrect, as every record referrers back to the original $F$3 and $H$3 between values in row 3.

I have tried the following to no avail;

1. Copying (dragging) the cells down.
2. Using the format painter.
3. Removing the $ before the row number.
4. Removing all $.
5. Referencing the between as tblTeeSelectionAnalysis[@Low] and blTeeSelectionAnalysis[@High].
6. Referencing the between as [@Low] and [@High].
7. Referencing the Applies to as tblTeeSelectionAnalysis[@Actual].
8. Any combination of the above I could think of.

I really, really, do not want to have to go in and create 60 rules.

Any help will be greatly appreciated.

Thanks,

Kathy

 

[dreid1011]

What is the actual rule you are using? Try this:

Book1
	F	G	H
1
2	Low	Actual	High
3	20	17.7	26.9
4	27	38.6	39.9
5	27		39.9
6	40	48.4	54
7	40	54	54
8	27	54	39.9
9	20	27.9	26.9
10	27	39.1	39.9
11	20	30.4	26.9
12	20	25.3	26.9
13	27	36.3	39.9
14	20	25.7	26.9
15	20	26.3	26.9
16	27	35.3	39.9
17	20	23.8	26.9
18	20	35.8	26.9
19	27	53.7	39.9
Sheet7

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G3:G19	Expression	=OR(G3<F3,G3>H3)	text	NO

 

[Kathy Belvel]

Thanks, I got it to work using the Conditional Formatting tool form the ribbon. I'm not sure why it didn't work before as I had already tried these settings (below). However, in checking the format settings if individual cells the F3 and H3 cell references remain F3 and H3 in for all cells, yet the formatting is now correct. Anyway, thank you.

One other question if I may, in your screen shot above you are using a "Cells with Conditional Formatting" tool. Is that an add in tool because is different as you can see from below? Just curious.

Kathy

 

[dreid1011]

One other question if I may, in your screen shot above you are using a "Cells with Conditional Formatting" tool. Is that an add in tool because is different as you can see from below? Just curious.

That is the display format of the XL2BB addin from the forum. I used the Conditional Formatting tool built into Excel. I also used "Use a formula to determine which cells to format" and you used "Format only cells that contain", though I don't think there should be any difference in outcome with either method.

And glad you got it working.