'--- 글꼴 서식 지정하기

Option Explicit

'--- 글꼴 서식 지정하기

Sub Sample061()

    Sheets("Sheet1").Select

    Dim rngCell As Range

    For Each rngCell In Range("G3:G9")

        If rngCell = "합격" Then

            With rngCell.Offset(0, -5).Resize(1, 6).Font

                .Name = "굴림"

                .Size = 11

                .Bold = True

            End With

        End If

    Next

End Sub

'--- 셀의 일부 문자에 대한 글꼴 서식

Sub Sample062()

    Sheets("Sheet2").Select

    Dim intA As Integer, intB As Integer

    For intA = 3 To 9

        intB = InStr(Cells(intA, "C"), "마포구")

        If intB > 0 Then Cells(intA, "c").Characters(intB, 3).Font.Size = 14

    Next

End Sub

'--- 글꼴 색과 채우기 색 설정

Sub Sample063()

    Sheets("Sheet3").Select

    Range("D2").Font.Color = RGB(255, 0, 255)

    Range("D2").Interior.Color = RGB(255, 255, 0)

End Sub

'--- 색 번호로 색 설정하기

Sub Sample064()

    Sheets("Sheet4").Select

    Dim intRow As Integer, intCol As Integer, intColor As Integer

    Range("B2:I8").Interior.ColorIndex = xlNone

    For intRow = 1 To 7

        For intCol = 1 To 8

            intColor = intColor + 1

            Range("B2:I8").Cells(intRow, intCol).Interior.ColorIndex = intColor

        Next

    Next

End Sub

'--- 테마 색 사용하기

Sub Sample065()

    Sheets("Sheet5").Select

    Range("B2").Font.ThemeColor = xlThemeColorDark1

    Range("B2").Interior.ThemeColor = xlThemeColorLight1

    Range("D2:D4").Interior.ThemeColor = xlThemeColorAccent6

    Range("D3").Interior.TintAndShade = 0.6

    Range("D4").Interior.TintAndShade = -0.3

End Sub

'--- 테두리 그리기

Sub Sample066()

    Sheets("Sheet6").Select

    Range("B2:F6").Borders.Weight = xlHairline

    Range("B2:F2").Borders(xlEdgeBottom).LineStyle = xlDouble

    Range("B2:F6").BorderAround Weight:=xlThick, ColorIndex:=5

End Sub

'--- 표시 형식 지정하기

Sub Sample067()

    Sheets("Sheet7").Select

    Range("B3:B9").NumberFormat = "@님"

    Range("C3:C9").NumberFormat = "yyyy년"

    Range("D3:E9").NumberFormat = "#,##0_-"

    Range("F3:F9").NumberFormat = "0.00%_-"

End Sub

'--- 셀의 맞춤 서식 지정하기

Sub Sample068()

    Sheets("Sheet8").Select

    Range("B2:B3").HorizontalAlignment = xlCenter

    Range("C2").HorizontalAlignment = xlRight

    Range("B4").Orientation = xlVertical

    With Range("C4")

        .VerticalAlignment = xlTop

        .IndentLevel = 1

        .WrapText = True

    End With

End Sub

'--- 셀 병합하기

Sub Sample069()

    Sheets("Sheet9").Select

    Range("B4:C8").Merge

    Range("E4:F8").Merge True

    Range("H4").UnMerge

End Sub

'--- 셀 병합 해제하기

Sub Sample070()

    Sheets("Sheet10").Select

    Dim rngCell As Range

    If Not TypeName(Selection) = "Range" Then Exit Sub

    If Selection.Count = 1 Then Exit Sub

    For Each rngCell In Selection

        If rngCell.MergeCells Then

            With rngCell.MergeArea

                .UnMerge

                .Value = rngCell

            End With

        End If

    Next

End Sub

'--- 셀 서식 지우기

Sub Sample071()

    Sheets("Sheet11").Select

    Range("B2, D2, F2").ClearFormats

    Range("B4").Interior.Color = xlNone

    Range("D4").Borders.LineStyle = xlNone

    Range("F4").NumberFormat = "General"

    Range("B6").HorizontalAlignment = xlGeneral

    Range("D6").Font.Name = Application.StandardFont

    Range("F6").Font.Size = Application.StandardFontSize

End Sub

'--- 셀 값 조건부 서식

Sub Sample072()

    Sheets("Sheet12").Select

    With Range("F3:F9")

        .FormatConditions.Delete

        .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:=0

        .FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, Formula1:=0

        .FormatConditions(1).Interior.Color = RGB(204, 255, 204)

        .FormatConditions(2).Interior.Color = RGB(255, 204, 255)

    End With

End Sub

'--- 수식 조건부 서식

Sub Sample073()

    Sheets("Sheet12").Select

    With Range("B3:F9")

        .FormatConditions.Delete

        .FormatConditions.Add Type:=xlExpression, Formula1:="=$F3>0"

        .FormatConditions(1).Interior.Color = RGB(204, 255, 204)

    End With

End Sub

'--- 평균 초과 조건부 서식

Sub Sample074()

    Sheets("Sheet13").Select

    Range("B2:E7").Formula = "=RANDBETWEEN(1,100)"

    With Range("B2:E7")

        .FormatConditions.Delete

        .FormatConditions.AddAboveAverage

        .FormatConditions(1).AboveBelow = xlAboveAverage

        .FormatConditions(1).Interior.Color = RGB(255, 204, 255)

    End With

End Sub

'--- Top 10 조건부 서식

Sub Sample075()

    Sheets("Sheet13").Select

    Range("B2:E7").Formula = "=RANDBETWEEN(1,100)"

    With Range("B2:E7")

        .FormatConditions.Delete

        .FormatConditions.AddTop10

        With .FormatConditions(1)

            .TopBottom = xlTop10Top

            .Rank = 10

            .Percent = False

            .Interior.Color = RGB(255, 204, 255)

        End With

    End With

End Sub

'--- 중복 값에 대한 조건부 서식

Sub Sample076()

    Sheets("Sheet13").Select

    Range("B2:E7").Formula = "=RANDBETWEEN(1,100)"

    With Range("B2:E7")

        .FormatConditions.Delete

        .FormatConditions.AddUniqueValues

        With .FormatConditions(1)

            .DupeUnique = xlDuplicate

            .Interior.Color = RGB(255, 204, 255)

        End With

    End With

End Sub

'--- 데이터 막대 조건부 서식

Sub Sample077()

    Sheets("Sheet14").Select

    With Range("E3:E9")

        .FormatConditions.Delete

        .FormatConditions.AddDatabar

        .FormatConditions(1).BarColor.Color = RGB(204, 0, 255)

    End With

End Sub

'--- 색조 조건부 서식

Sub Sample078()

    Sheets("Sheet14").Select

    With Range("E3:E9")

        .FormatConditions.Delete

        .FormatConditions.AddColorScale ColorScaleType:=2

        .FormatConditions(1).ColorScaleCriteria(1).FormatColor.Color = RGB(0, 255, 0)

        .FormatConditions(1).ColorScaleCriteria(2).FormatColor.Color = RGB(255, 255, 255)

    End With

End Sub

'--- 아이콘 집합 조건부 서식

Sub Sample079()

    Sheets("Sheet14").Select

    With Range("E3:E9")

        .FormatConditions.Delete

        .FormatConditions.AddIconSetCondition

        .FormatConditions(1).IconSet = ActiveWorkbook.IconSets(xl3Flags)

    End With

End Sub

'--- 범위를 엑셀 표로 변환하기

Sub Sample080()

    Sheets("Sheet15").Select

    Range("B2").Select

    ActiveSheet.ListObjects.Add.Name = "실적표"

    ActiveSheet.ListObjects("실적표").TableStyle = "TableStyleMedium4"

End Sub

'--- 엑셀 표에 요약 행 추가하기

Sub Sample081()

    Sheets("Sheet15").Select

    Dim listTemp As ListObject

    Set listTemp = ActiveSheet.ListObjects("실적표")

    With listTemp

        .ShowTotals = True

        .ListColumns("전년도실적").TotalsCalculation = xlTotalsCalculationSum

        .ListColumns("금년도실적").TotalsCalculation = xlTotalsCalculationSum

    End With

End Sub

'--- 엑셀 표를 범위로 변환하기

Sub Sample082()

    Sheets("Sheet15").Select

    Dim listTemp As ListObject

    Set listTemp = ActiveSheet.ListObjects("실적표")

    With listTemp

        .ShowTotals = False

        .TableStyle = ""

        .Unlist

    End With

End Sub

'--- 엑셀 표의 행/열 참조

Sub Sample083()

    Sheets("Sheet15").Select

    Dim listTemp As ListObject

    Range("B2").Select

    Set listTemp = ActiveSheet.ListObjects.Add

    With listTemp

        .Name = "실적표"

        .TableStyle = "TableStyleMedium4"

        .ShowTotals = True

        .ListColumns("전년도실적").TotalsCalculation = xlTotalsCalculationSum

        .ListColumns("금년도실적").TotalsCalculation = xlTotalsCalculationSum

    End With

End Sub

Sub Sample083_1()

    Sheets("Sheet16").Select

    Dim listTemp As ListObject

    Set listTemp = ActiveSheet.ListObjects("실적표_2")

    '--엑셀 표 전체 선택

    'listTemp.Range.Select

    'Range("실적표_2[#All]").Select

    '--엑셀 표의 데이터 범위만 선택

    'listTemp.DataBodyRange.Select

    'Range("실적표_2").Select

    '---열 전체 참조

    'listTemp.ListColumns(3).Range.Select

    'listTemp.ListColumns("금년도실적").Range.Select

    '---열 데이터 범위만 참조

    'Range("실적표_2").Columns(4).Select

    'Range("실적표_2[증감율]").Select

    '---행 전체 참조

    'listTemp.ListRows(5).Range.Select

    '---제목 행 참조

    'listTemp.HeaderRowRange.Select

    '---요약 행 참조

    'listTemp.TotalsRowRange.Select

End Sub