Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting with VLookup and Offset functions | Excel Worksheet Functions | |||
ActiveCell.Offset(i, 0) = Chr(ActiveCell.Row + 62) returns all A's | Excel Programming | |||
Offset in Conditional Formatting? | Excel Discussion (Misc queries) | |||
Conditional formatting offset reference | Excel Worksheet Functions | |||
If activecell.column = variable then activecell,offset (0,1) | Excel Discussion (Misc queries) |