Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with ComboBox, dynamic range and change event | Excel Programming | |||
Range object and worksheet change event | Excel Programming | |||
Change Event Range Help | Excel Programming | |||
Change event triggered by a named range | Excel Programming | |||
Change Event on a named range | Excel Programming |