Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default 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


  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default 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
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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


  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditional Formatting with VLookup and Offset functions Phrank Excel Worksheet Functions 2 January 10th 16 10:51 AM
ActiveCell.Offset(i, 0) = Chr(ActiveCell.Row + 62) returns all A's Howard Excel Programming 5 December 25th 12 05:08 AM
Offset in Conditional Formatting? sly411 Excel Discussion (Misc queries) 2 March 22nd 10 09:53 PM
Conditional formatting offset reference IanC[_2_] Excel Worksheet Functions 3 February 17th 10 05:58 PM
If activecell.column = variable then activecell,offset (0,1) Battykoda via OfficeKB.com Excel Discussion (Misc queries) 1 October 2nd 07 08:05 PM


All times are GMT +1. The time now is 01:07 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"