Conditional formatting activecell offset
 

I assume to use Worksheet_SelectionChange(ByVal Target As Range)

Whe

if I select cell A2 then cell C1 is highlighted.
if I select cell A3 then cell E1 is highlighted.
if I select cell A4 then cell G1 is highlighted.
etc.
etc.

I think I can carry it on over to column Y if I can see what the first couple of formula or code should be...

I figure to use one of these to clear previous fill colors from the range on each selection.

Range("C1:Y1").Interior.ColorIndex = xlNone
Range(Cells(1, 3), Cells(1, 25)).Interior.ColorIndex = xlNone

If it can be done on the sheet conditional formatting function that would be just fine. I could not figure out the formula for a TRUE for the selection cell or column header cell.

Thanks,
Howard

Conditional formatting activecell offset
 

Hi Howard,

Am Sat, 27 Feb 2016 23:30:41 -0800 (PST) schrieb L. Howard:

if I select cell A2 then cell C1 is highlighted.
if I select cell A3 then cell E1 is highlighted.
if I select cell A4 then cell G1 is highlighted.
etc.
etc.

try:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("A2:A13")) Is Nothing _
Or Target.Count 1 Then Exit Sub

ActiveSheet.UsedRange.Interior.Color = xlNone
Cells(1, Target.Row * 2 - 1).Interior.Color = vbRed
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Conditional formatting activecell offset
 

Hi again,

Am Sun, 28 Feb 2016 08:47:58 +0100 schrieb Claus Busch:

try:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

better try:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Range("C1:Y1").Interior.Color = xlNone

If Intersect(Target, Range("A2:A13")) Is Nothing _
Or Target.Count 1 Then Exit Sub

Cells(1, Target.Row * 2 - 1).Interior.Color = vbRed
End Sub



Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Conditional formatting activecell offset
 

Hi Claus,

Both work just as I need. Thanks much.

A bit tricky, I can see for sure I would never got it correct.

Thanks again.

Howard

Conditional formatting activecell offset
 

Hi Howard,

Am Sun, 28 Feb 2016 02:08:48 -0800 (PST) schrieb L. Howard:

A bit tricky, I can see for sure I would never got it correct.

it is easy:

1. The highlighted column depends on the selected row number
2. So you want to highlight every second column you have to multiply the
row number by 2
3. You start selection in row 2 and need column 3. So you have to
substract 1
column number = target.row *2 - 1

Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Conditional formatting activecell offset
 

it is easy:

1. The highlighted column depends on the selected row number
2. So you want to highlight every second column you have to multiply the
row number by 2
3. You start selection in row 2 and need column 3. So you have to
substract 1
column number = target.row *2 - 1

Regards
Claus B.


Hi Claus,

Yes, brilliant in the simplicity, I was knocking around with the OFFSET function and not getting much done.

Howard
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional