Worksheet.Change Event (Excel)

아티클
06/17/2015

Occurs when cells on the worksheet are changed by the user or by an external link.

Syntax

식 .Change(Target)

식 A variable that represents a Worksheet object.

Parameters

Name	Required/Optional	Data Type	Description
Target	필수	Range	The changed range. Can be more than one cell.

Return Value

Nothing

Remarks

This event does not occur when cells change during a recalculation. Use the Calculate event to trap a sheet recalculation.

Example

The following code example changes the color of changed cells to blue.

Private Sub Worksheet_Change(ByVal Target as Range) 
    Target.Font.ColorIndex = 5 
End Sub

Sample code provided by: MVP 기고자 Bill Jelen, MrExcel.com | About the Contributors

The following code example verifies that, when a cell value changes, the changed cell is in column A, and if the changed value of the cell is greater than 100. If the value is greater than 100, the adjacent cell in column B is changed to the color red.

Private Sub Worksheet_Change(ByVal Target As Excel.Range) 
    If Target.Column = 1 Then 
        ThisRow = Target.Row 
        If Target.Value > 100 Then 
            Range("B" & ThisRow).Interior.ColorIndex = 3 
        Else 
            Range("B" & ThisRow).Interior.ColorIndex = xlColorIndexNone 
        End If 
    End If 
End Sub

Sample code provided by: MVP 기고자 Tom Urtis, Atlas Programming Management | About the Contributors

The following code example sets the values in the range A1:A10 to be uppercase as the data is entered into the cell.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1:A10")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
    Application.EnableEvents = False
    'Set the values to be uppercase
    Target.Value = UCase(Target.Value)
    Application.EnableEvents = True
End Sub

About the Contributors

MVP인 Bill Jelen은 Microsoft Excel에 대한 서적을 다수 집필했습니다. 또한 Leo Laporte와 함께 TechTV에 고정 출연하고 있으며, Excel과 관련한 30만 건 이상의 질문과 대답이 제공되는 MrExcel.com(영문일 수 있음)의 운영자이기도 합니다.

MVP인 Tom Urtis는 Microsoft Office 및 Excel 비즈니스 솔루션 관련 원스톱 서비스 업체인 실리콘 밸리 소재 Atlas Programming Management(영문일 수 있음)의 설립자입니다. Tom은 25년 이상 비즈니스 관리 업무를 맡고 Microsoft Office 응용 프로그램을 개발해 왔으며, "Holy Macro! It’s 2,500 Excel VBA Examples"를 공동 집필하기도 했습니다.

참고 항목

개념

Worksheet Object

Worksheet Object Members

다음을 통해 공유