VBA

셀에서 글자 변경시 특정 셀의 색 배경색 변경

손병환 2006. 1. 3. 19:38

Private Sub Workbook_Open()
    Dim i As Integer
    For i = 4 To 100
        If Sheet1.Range("A" & i & ":H" & i).FormatConditions.Count <> 0 Then
            Sheet1.Range("A" & i & ":H" & i).FormatConditions.Delete

        End If


        With Sheet1.Range("A" & i & ":H" & i).FormatConditions.Add(xlｅxpression, , "=$H$" & i & "=""完了""")
            .Interior.Color = RGB(200, 200, 200)
        End With
       
        With Sheet1.Range("A" & i & ":H" & i).FormatConditions.Add(xlｅxpression, , "=$H$" & i & "=""保留""")
            .Interior.Color = RGB(200, 200, 255)
        End With
       
        With Sheet1.Range("A" & i & ":H" & i).FormatConditions.Add(xlｅxpression, , "=$H$" & i & "=""""")
            .Interior.Color = RGB(255, 255, 255)
        End With
    Next i
End Sub

 

 


Private Sub Worksheet_Activate()
    Dim i As Integer
    For i = 6 To 100
        If Sheet1.Range("A" & i & ":AB" & i).FormatConditions.Count <> 0 Then
            Sheet1.Range("A" & i & ":AB" & i).FormatConditions.Delete

        End If


        With Sheet1.Range("A" & i & ":AB" & i).FormatConditions.Add(xlｅxpression, , "=$Y$" & i & "<>""""")
            .Interior.Color = RGB(200, 200, 200)
        End With
    Next i
End Sub