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,

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,