Conditional formatting with offset

[Jeff SG]

Hello,

I have a named range in my worksheet. which spans roughly ("B2:AB2000")
within this range are single text words ie: Rent, Property, Signage. etc.

Ive created formatting rules for each word, filling the cell with a unique color, using: Conditional Formatting / New Rule / Format only cells that contain / Format cells with specific text.

How can i offset their color formatting 10 cells to the left, instead of appearing directly on each word.

Thanks

[Paul]

Select your range, B2:AB2000. Then, instead of using the 'Format only cells that contain' option, use 'Use a formula to determine which cells to format' and put this formula below:

Please Login or Register  to view this content.

I used L2, since that is 10 columns to the right of column B, your first column. After applying the conditional formatting, if cell N5 contained the word Rent, then cell D5 would be shaded the color you chose in CF. Note that if one of those words exists in a column before K, nothing will be formatted because 10 columns to the left of J doesn't exist.

[Jeff SG]

Thanks Paul..

It works great!

But im running into a problem where the Conditional Formatting doesn't hold to the cell.

If a specific word is in say, F20, and i have its color offset 3 cells to the right at C20. When I move that colored cell( C20), it also moves the color along with it. Not sure why this would happen, as it should be referencing the initial offset code.

Is there a way around this?, or perhaps a refresh option? Ive tried F2+Enter which doesn't work.

thanks!

[Paul]

The quick answer is to not move cells, as the cell properties get moved along with it. If you do that, then go into the Conditional Formatting screen to Manage Rules, then select 'This Worksheet' at the top (to see all rules), you'll notice that there is now an additional rule that applies just to that new cell location, and the main CF formula now 'Applies To' a convoluted range that excludes the original cell.

So originally you had one rule [=OR(E2="Rent",E2="Signage",E2="Property")] with an 'Applies To' of [=$B$2:$AB$2000].

If you drag C3 to D9, a new rule gets created that refers back to the original cell's offset (F3) and only applies to $D$9. The original rule still exists, but now applies to all cells in $B$2:$AB$2000 *except* cell D9. (Applies To: =$B$2:$AB$2,$B$4:$AB$8,$D$3:$AB$3,$B$10:$AB$30,$B$9:$C$9,$E$9:$AB$9,$B$3)

So unless there's another way, after moving a cell you'd need to modify the original CF rule to apply to the correct range again (=$B$2:$AB$2000) and delete the single-cell rule.

[jeff p]

Thanks for the detailed explanation, Paul

Didn't realize that about Conditional formatting with an offset. I guess it may not work for my needs. I'll try another approach.

Many thanks!