VBA 유튜브 학습기 12 - 드롭다운 메뉴에서 원하는 셀 값을 선택하면 그 셀 값이 포함된 데이터 행 음영으로 표시하기(Change 이벤트)
엑사남 님 강의 12강 듣고 왔습니다!
오늘은 셀에서 드롭다운(유효성 검사 목록) 메뉴 중 원하는 셀 값을 선택하면 해당 값에 해당하는 데이터 행을 노란색 음영으로 표시해 주는 방법에 대해 배웠습니다. (이 집 설명 장황하네) 😴💤
뭐 그냥 간단하게 실무에서 자주 쓸 거 같은 그런 기능입니다!
이런 걸 Change 이벤트라고 한다네요.
전에 SelectionChange 이벤트에 대해서 배웠는데, 두 개는 이런 차이가 있습니다.
* SelectionChange : 클릭했을 때 반응
* Change : 값이 변경되었을 때 반응
공부한 내용 까먹기 전에 바로 정리해 볼게요~ 스타트!📚
오늘 코드를 짤 내용은 이런 겁니다.
이렇게 시트의 드롭다운 메뉴(유효성 검사 목록)에서 특정 값(남 또는 여)를 선택하면
밑에 표에서 그 해당 값이 포함된 행의 채우기 컬러를 노란색으로 표시하게 할 겁니다.
Step1. 사전 세팅
Alt + F11 누르고요.
VBA 편집 창 왼쪽 프로젝트 목록에서 시트명을 더블 클릭합니다.
이 시트 내에서 동작하게 할 거라서 모듈을 삽입하지 않고 시트를 더블 클릭하고 작성합니다.
시트명을 더블 클릭하면 이런 창이 뜨는데요.
여기서 (일반)을 누르면 이렇게 드롭 다운 메뉴가 펼쳐지거든요.
여기서 'Worksheet'를 선택하고요.
오른쪽의 이벤트 목록 드롭 다운 메뉴에서는 'Change'를 클릭해 줍니다.
그럼 코드 작성 칸에 이렇게 뭔가 Sub문이 두 개가 들어 있을 거예요.
처음에 SelectionChange로 되어 있던 것을 Change로 바꿔서 Sub문이 두 개가 됐는데.
SelectionChange에 해당하는 Sub문을 지워줍니다.
시험 공부 전 책상 정리하듯 장황한 준비가 끝났다면 이제 코드를 작성해 봅니다.
Step2. 변수 설정
코드 작성의 기본 중의 기본.
변수 설정부터 시작합니다.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim strS As String
Dim rngR As Range
End Sub
strS이란 변수는 String으로 지정했고요.
이후에 G2 셀을 이 strS라는 변수에 대입할 겁니다.
rngR이란 변수는 Range로 지정했고요.
이후에 F7:L26 표를 이 rngR에 대입합니다.
위에서 얘기한 대로 strS와 rngR에 각각의 값과 범위를 지정해 줍니다.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim strS As String
Dim rngR As Range
strS = Target
Set rngR = Range("F7:L26")
End Sub
strS = Target
'strS를 타깃(선택된 셀)으로 지정합니다.
Set rngR = Range("F7:L26")
'rngR에 F7:F26의 범위를 지정합니다.
그런데 여기서 중요한 게 있습니다.
데이터가 아닌 객체(셀, 범위, 시트, 도형 등)를 변수에 지정할 때는
'Set 변수명 = 객체' 형식으로 코드를 작성한다고 하네요.
Step 3. 코드 작성
Private Sub Worksheet_Change(ByVal Target As Range)
Dim strS As String
Dim rngR As Range
Dim i As Long
strS = Target
Set rngR = Range("F7:L26")
If Intersect(Target, Range("G2")) Is Nothing Then Exit Sub
For i = 7 To 26
If Range("G" & i) = strS Then
Range("F" & i).Resize(1, 7).Interior.ColorIndex = 6
End If
Next i
End Sub
그다음 계속해서 코드를 작성해 나가 봅니다.
If Intersect(Target, Rnage("G2")) Is Nothing Then Exit Sub
' 선택된 셀(Target)이 G2 셀의 값이 아니라면 Sub문을 빠져나가라.
For i 7 To 26
'i는 7부터 26까지를 반복한다.
If Range("G" & i) = strS Then
'G열의 i행이 선택된 셀(strS = Target)의 값과 같다면
Range("F" & i).Resize(1,7).Interior.ColorIndex = 6
'F열의 i행을 1행, 7열의 범위만큼 잡아서(Resize(1,7)) 셀 채우기 컬러를 노란색(6)으로 바꿔라.
이렇게 코드를 작성한 후에 실행해 보면
G2 셀에서 '남'을 선택하면
이렇게 F열의 값이 '남'인 데이터 행들의 채우기 컬러가 노란색으로 바뀝니다.
이번엔 G2 셀의 값을 '여'로 바꿔 볼게요.
????? 왜죠? 🙄🙄🙄🤯😨
G2 셀의 값이 바뀔 때 다시 셀의 채우기 컬러를 흰색으로 바꾸는 코드가 없다 보니까
노란색으로 셀 채우기 컬러를 바꾸는 것만 되는 겁니다.
Step 4. 코드 수정
G2 셀의 값이 바뀔 때 셀의 채우기 컬러를 흰색으로 바꿔 주는 코드를 추가해 보겠습니다.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim strS As String
Dim rngR As Range
Dim i As Long
strS = Target
Set rngR = Range("F7:L26")
If Intersect(Target, Range("G2")) Is Nothing Then Exit Sub
rngR.Interior.ColorIndex = 0
For i = 7 To 26
If Range("G" & i) = strS Then
Range("F" & i).Resize(1, 7).Interior.ColorIndex = 6
End If
Next i
End Sub
위치는 For 반복문을 돌기 전에 맞겠죠?
rngR.Interior.ColorIndex = 0
'F7:L26 범위의(rngR) 셀 채우기 컬러를(Interior.ColorIndex) 흰색(0)으로 지정하라.
코드를 추가하고 실행해 봅니다.
먼저 G2 셀을 '남'으로 지정해 볼게요.
이렇게 F열의 값이 '남'인 행들의 셀 채우기 컬러가 노란색으로 바뀌고요.
아까 제대로 작동하지 않았던 G2 셀이 '여'일 경우를 보겠습니다.
오호! F열이 '남'인 셀의 값이 흰색으로 바뀌고, G2 셀의 값에 해당하는 '여' 데이터 관련 행만 컬러가 노란색으로 바뀝니다.
여기서 끝?
아닙니다. 아니래요.
Step 5. 오류 방지 구문 추가
실무에서 엑셀을 사용해 보면, 엑셀을 여러 사람이 사용하잖아요.
그러다 보면 꼭 빌런이 있기 마련입니다. 제가 제일 싫어하는 빌런은 자꾸 테두리 날려 먹는 테두리 빌런인데요.
개빡쳐 🤬
이 데이터에서는 이렇게 어느 정도의 범위를 잡고 Delete를 누르면
이렇게 바로 런타임 오류 창이 나타나거든요.
오류가 나는 이유는 strS = Target으로 변수를 설정했는데.
즉, strS = 셀 1개인데 여러 범위를 잡고 어떤 액션을 취해서 라고 합니다.
(사실 이 부분이 클리어하게 이해가 되진 않습니다.... 저는 오류의 이유를 Target 즉, G2 셀의 데이터가 날아가서라고 생각했거든요. 뭐 아무튼....전 쪼렙이니까 일단 오류 이유를 강제로 암기해 봅니다. 모르겠으면 그냥 외웁니다 ㅋㅋㅋ)
이걸 미연에 방지하기 위한 오류 방지 코드를 하나 추가로 심습니다.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim strS As String
Dim rngR As Range
Dim i As Long
If Target.Count > 1 Then Exit Sub
strS = Target
Set rngR = Range("F7:L26")
If Intersect(Target, Range("G2")) Is Nothing Then Exit Sub
rngR.Interior.ColorIndex = 0
For i = 7 To 26
If Range("G" & i) = strS Then
Range("F" & i).Resize(1, 7).Interior.ColorIndex = 6
End If
Next i
End Sub
If Target.Count > 1 Then Exit Sub
'선택된 셀(Target)의 개수가(Count) 1개보다 많으면(>1) Sub문을 빠져나가라.(Exit Sub)
Step 6. 구문 배치 정리
작성해 주고 나니, Exit Sub 상황에 대한 구문을 모아 두는 게 더 보기 좋을 거 같아서 구문 배치를 정리해 봅니다.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim strS As String
Dim rngR As Range
Dim i As Long
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("G2")) Is Nothing Then Exit Sub
strS = Target
Set rngR = Range("F7:L26")
rngR.Interior.ColorIndex = 0
For i = 7 To 26
If Range("G" & i) = strS Then
Range("F" & i).Resize(1, 7).Interior.ColorIndex = 6
End If
Next i
End Sub
이렇게 Exit Sub에 관련된 구문을 모아 놓았어요.
사실 저렇게 안 해도 실행에는 아무 문제가 없습니다.
그래도 저렇게 모아 두면 나중에 코딩이 복잡해졌을 때 조금 더 가독성이 좋겠죠?
12강 내용은 여기까지고요.
12강도 과제가 있었습니다.
[과제]
선택한 성별 중에서 평균이 70점 이상이 되는 행만 색상이 바뀌게 코딩하세요.
🤔
기존 코드에 '평균이 70점 이상이 되는 행'이라는 조건만 추가해 주면 될 것 같습니다.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim strS As String
Dim rngR As Range
Dim i As Long
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("G2")) Is Nothing Then Exit Sub
strS = Target
Set rngR = Range("F7:L26")
rngR.Interior.ColorIndex = 0
For i = 7 To 26
If Range("G" & i) = strS And Range("L" & i) >= 70 Then
Range("F" & i).Resize(1, 7).Interior.ColorIndex = 6
End If
Next i
End Sub
If문에
And Range("L" & i) >70 구문만 추가해 줬습니다.
'L열 i행이 70보다 큰 경우에는
이라는 뜻이죠.
코드를 실행해 보면
이렇게!
G2 셀에 선택된 '여' 중에서 평균이 70 이상인 경우에만 표시가 됐습니다.
과제 끝!
저는 이제 13강 들으러 갈게요~
뒤로 갈수록 정리할 분량이 늘어나서 1일 1강이 안 되네요😥