change event for cells within a range
 

Hi,

I want to run a macro if a cell within a certain range is changed by a
user.

eg, if a cell is changed within the range A1:A10 then the text color
of that cell changes from black to red.

can you help?

The only code I have so far is:-



Private Sub Worksheet_Change(ByVal Target As Range)

Target = Range("A1:A10")

End Sub

kind regards,
Matt

change event for cells within a range
 

You need to use the intersect method like this:

Sub Run_in_range()
If Intersect(ActiveCell, Range("A1:A10")) Is Nothing Then
MsgBox "The active cell does NOT Intersect A1:A10 " &
Activecell.address
Else
MsgBox "The active cell does Intersect A1:A10 " & Activecell.address
'your call for a macro or your code here
End If
End Sub


--
The Code Cage Team

Regards,
The Code Cage Team
www.thecodecage.com
------------------------------------------------------------------------
The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=25998

change event for cells within a range
 

Hi,

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
Target.Font.ColorIndex = 3
End If
End Sub

Mike

"MJKelly" wrote:


Hi,

I want to run a macro if a cell within a certain range is changed by a
user.

eg, if a cell is changed within the range A1:A10 then the text color
of that cell changes from black to red.

can you help?

The only code I have so far is:-



Private Sub Worksheet_Change(ByVal Target As Range)

Target = Range("A1:A10")

End Sub

kind regards,
Matt

change event for cells within a range
 

Mike,

That worked a treat. One further query, I want to paste data to the
range during a weekly setup. And do not want the event to trigger
until this has been done. How can I not have the event triggered in
this instance? The idea being that the original data is in grey text
and not bold and the amendments to the data are Black/Bold.

code now reads:-

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A1:A10", "C1:C10")) Is Nothing Then
With Target
.Font.ColorIndex = 1
.Font.Bold = True
End With
End If

End Sub

Thanks,
Matt

change event for cells within a range
 

Hi,

If your pasting data then the chances are your pasting multiple cells so
this as a first line would work

If Target.Cells.Count 1 Then Exit Sub

On the other hand a 100% catch all would be to modify the code thus

If Not Intersect(Target, Range("A1:A10", "C1:C10")) Is Nothing Then
response = MsgBox("Process Bolding?", vbYesNo)
If response = vbNo Then Exit Sub
With Target
.Font.ColorIndex = 1
.Font.Bold = True
End With
End If

End Sub

Mike


"MJKelly" wrote:

Mike,

That worked a treat. One further query, I want to paste data to the
range during a weekly setup. And do not want the event to trigger
until this has been done. How can I not have the event triggered in
this instance? The idea being that the original data is in grey text
and not bold and the amendments to the data are Black/Bold.

code now reads:-

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A1:A10", "C1:C10")) Is Nothing Then
With Target
.Font.ColorIndex = 1
.Font.Bold = True
End With
End If

End Sub

Thanks,
Matt