Highlight a (different) cell based on current active cell

**scamp53** · 05-19-2012, 09:32 AM

Hi - I've tried searching the forum and web for this without success.

On the same sheet I have two data areas identical in size a1:o15 and a16:o30
First area has data which I need to look at while moving through second area so ...
I want to highlight the (logically) linked cell based on current cell (which will be in the second area).

i.e. if I move into cell B18, I want cell B3 to change fill colour while I'm in the cell B18
(then I move to D19 and want D4 highlighted etc).

I've tried mucking around with conditional formatting and using CELL "address" with INDIRECT and ADDRESS but can't get close.
As an intermediate step, I've got a Worksheet_SelectionChange sub updating a fixed cell with the current cell address but don't seem to be able to use that info.

Would very much appreciate help
Thanks, Ian

**Marcol** · 05-19-2012, 10:44 AM

Try this ...

In the sheet module for your desired sheet

Please Login or Register  to view this content.

See this workbook

**scamp53** · 05-19-2012, 09:30 PM

Hi - thanks for replying. On my sheet (and yours) that appears to be highlighting the row and column headings of the active cell i.e. A and 16 (say).
The cell (not row and column heading) I need highlighted is the cell in the first range i.e. the 'offset' cell from the active cell.

I may not have explained the issue properly as you have quickly come up with an answer I couldn't have arrived at - do I need to re-explain ??
Help anyone ??

**protonLeah** · 05-19-2012, 10:09 PM

Try this one:

**scamp53** · 05-19-2012, 10:43 PM

Hi - is it possible I've got some setup that is affecting this. I've copied your code across and it appears to do nothing. As it's similar to the previous response, it must be something my end?
I've tried substituting my range for 'LowerTable' and also naming the LowerTable range. I've also tried saving as xlsm, xls and xlsx.
I can't get your Trail-it sheet to work either. Cells G8 and G23 appear to have green fill formatting fixed but if I move the active cell around the bottom table nothing happens.
I'm starting to think I'm thick?
Any thoughts about what I might be missing?

**Marcol** · 05-20-2012, 05:46 AM

Since both solutions offered so far are very similar, best post your workbook with an "active" cell and it's offset marked as you need.
Disguise any sensitive data.

**scamp53** · 05-20-2012, 08:09 AM

Hi - good idea and thanks. No need to worry about sensitive data as you'll see :-)
The (currently) active cell is G21 in the lowertable.
The cell to be highlighted is G6 - i.e. this column, row -15

Thanks again

**Marcol** · 05-20-2012, 10:06 AM

See if this does as you need.

Note
1/. Your conditional formatting will over-ride this code.
2/. Multiple cells can be selected, highlighting multiple offsets.

**scamp53** · 05-20-2012, 06:06 PM

Thanks so much for continuing this. So it seems the problem is my conditional formatting. As I need to retain that, it seems I'll have to look at some other method of highlighting the cell using your selection method (borders, font?). I hope that's do-able - won't be able to revisit it for a day or so but looking forward to testing.