I'm trying to create a validation macro to enter text into the cell of range
G:G depending on the contents of the adjacent cells in columns C and D. This
is what I have done to start with, there are other ElseIf entries to be
added, but it is really slow to start with.

Private Sub Worksheet_Calculate()
Dim myC As Range
Dim WatchRange1 As Range

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

Set WatchRange1 = Range("G2:G500")

For Each myC In WatchRange1

If myC.Offset(0, -3).Value = "Contract1" And _
myC.Offset(0, -4).Value = "Status A" Then
myC.Cells.Value = "Response A"
ElseIf myC.Offset(0, -3).Value = "Contract 2" And _
myC.Offset(0, -4).Value = "Status A" Then
myC.Cells.Value = "Response B"
Else: myC.Cells.Value = ""
End If

Next myC

With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End Sub

Putting into a calculate event calculates everything. I would write a bit
differently using cells(

for i= 2 to cells(rows.count,"g").end(xlup).row
if cells(i,"c")="sa" and cells(i,"d")="c1" then cells(i,"g")="aa"
if cells(i,"c")="sa" and cells(i,"d")="c2" then cells(i,"g")="bb"
if cells(i,"c")="sb" and cells(i,"d")="c1" then cells(i,"g")="aa1"
..etc with ONE line IFs with NO end if required for one liners
next i

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"DDawson" wrote in message
...
I'm trying to create a validation macro to enter text into the cell of
range
G:G depending on the contents of the adjacent cells in columns C and D.
This
is what I have done to start with, there are other ElseIf entries to be
added, but it is really slow to start with.

Private Sub Worksheet_Calculate()
Dim myC As Range
Dim WatchRange1 As Range

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

Set WatchRange1 = Range("G2:G500")

For Each myC In WatchRange1

If myC.Offset(0, -3).Value = "Contract1" And _
myC.Offset(0, -4).Value = "Status A" Then
myC.Cells.Value = "Response A"
ElseIf myC.Offset(0, -3).Value = "Contract 2" And _
myC.Offset(0, -4).Value = "Status A" Then
myC.Cells.Value = "Response B"
Else: myC.Cells.Value = ""
End If

Next myC

With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End Sub

Use the worksheet Change event rather than the Calculate event. Target will
allow you to examine only a single pair of cells rather than all the pairs of
cells.
--
Gary''s Student - gsnu200769


"DDawson" wrote:

I'm trying to create a validation macro to enter text into the cell of range
G:G depending on the contents of the adjacent cells in columns C and D. This
is what I have done to start with, there are other ElseIf entries to be
added, but it is really slow to start with.

Private Sub Worksheet_Calculate()
Dim myC As Range
Dim WatchRange1 As Range

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

Set WatchRange1 = Range("G2:G500")

For Each myC In WatchRange1

If myC.Offset(0, -3).Value = "Contract1" And _
myC.Offset(0, -4).Value = "Status A" Then
myC.Cells.Value = "Response A"
ElseIf myC.Offset(0, -3).Value = "Contract 2" And _
myC.Offset(0, -4).Value = "Status A" Then
myC.Cells.Value = "Response B"
Else: myC.Cells.Value = ""
End If

Next myC

With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End Sub