Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Name groups of .offset ranges
Hello everyone,
I'm a bit stuck and I would really appreciate your help. Using Worksheet_Change, I am formatting rows depending on a value in a cell. Offset works perfect. But I have recurrent formatting instances, with different combination and I don't know how to name the blocks of ".offset" instructions to be able to use them without re-writing the same blocks over and over again. Thanks in advance for any light you may give me! This is a sample of the code I'm using: ------------------------ Private Sub Worksheet_Change(ByVal target As Range) If Not Application.Intersect(target, Range("F10,F12,F14")) Is Nothing Then With target Select Case .Value Case "First": .Offset(0, 7).Resize(1, 1).Cells.ClearContents .Offset(0, -5).Resize(1, 4).Interior.ColorIndex = xlNone .Offset(0, -1).Resize(1, 7).Interior.ColorIndex = 35 Case "Second": .Offset(0, 7).Resize(1, 1).Value = "OK" .Offset(0, -2).Resize(1, 21).Interior.ColorIndex = 40 .Offset(0, -3).Resize(1, 3).Cells.Locked = True End Select End With End If End sub --------------- Regards, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Name groups of .offset ranges
Perhaps you could Call a Sub routine from your Select Case statement:
Case "First" Call First Case "Second" Call Second Sub First () .Offset(0, 7).Resize(1, 1).Cells.ClearContents .Offset(0, -5).Resize(1, 4).Interior.ColorIndex = xlNone .Offset(0, -1).Resize(1, 7).Interior.ColorIndex = 35 End Sub Sub Second () .Offset(0, 7).Resize(1, 1).Value = "OK" .Offset(0, -2).Resize(1, 21).Interior.ColorIndex = 40 .Offset(0, -3).Resize(1, 3).Cells.Locked = True End Sub This is just an idea, not the actual code. You would need a Public variable to pass the range of Target to the called routine. Mike F "JMJ" wrote in message ... Hello everyone, I'm a bit stuck and I would really appreciate your help. Using Worksheet_Change, I am formatting rows depending on a value in a cell. Offset works perfect. But I have recurrent formatting instances, with different combination and I don't know how to name the blocks of ".offset" instructions to be able to use them without re-writing the same blocks over and over again. Thanks in advance for any light you may give me! This is a sample of the code I'm using: ------------------------ Private Sub Worksheet_Change(ByVal target As Range) If Not Application.Intersect(target, Range("F10,F12,F14")) Is Nothing Then With target Select Case .Value Case "First": .Offset(0, 7).Resize(1, 1).Cells.ClearContents .Offset(0, -5).Resize(1, 4).Interior.ColorIndex = xlNone .Offset(0, -1).Resize(1, 7).Interior.ColorIndex = 35 Case "Second": .Offset(0, 7).Resize(1, 1).Value = "OK" .Offset(0, -2).Resize(1, 21).Interior.ColorIndex = 40 .Offset(0, -3).Resize(1, 3).Cells.Locked = True End Select End With End If End sub --------------- Regards, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Ranges and Offset | Excel Worksheet Functions | |||
How do I calculate an offset between two ranges? | Excel Programming | |||
Using Offset with named ranges | Excel Worksheet Functions | |||
OFFSET function to pick up monthly groups of data | Excel Worksheet Functions | |||
Ranges using offset | Excel Programming |