Wednesday, March 22, 2017

macro to set cell background color

Sub RoundToZero1()
    For Counter = 1 To 300
        For Col = 1 To 50
            Set curCell = Worksheets("Assets").Cells(Counter, Col)
            If curCell.Interior.Color = 12639228 Then  'RGB(252, 219, 192) Then
                curCell.Interior.Color = RGB(210, 231, 197)
            End If
            If curCell.Interior.Color = 2327285 Then  'RGB(252, 219, 192) Then
                curCell.Interior.Color = RGB(96, 148, 61)
            End If
            'If Abs(curCell.Value) < 0.01 Then curCell.Value = 0
        Next Col
    Next Counter
    For Counter = 1 To 300
        For Col = 1 To 50
            Set curCell = Worksheets("Liabilities").Cells(Counter, Col)
            If curCell.Interior.Color = 12639228 Then  'RGB(252, 219, 192) Then
                curCell.Interior.Color = RGB(210, 231, 197)
            End If
            If curCell.Interior.Color = 2327285 Then  'RGB(252, 219, 192) Then
                curCell.Interior.Color = RGB(96, 148, 61)
            End If
            'If Abs(curCell.Value) < 0.01 Then curCell.Value = 0
        Next Col
    Next Counter
    For Counter = 1 To 300
        For Col = 1 To 50
            Set curCell = Worksheets("IncomeStatement").Cells(Counter, Col)
            If curCell.Interior.Color = 12639228 Then  'RGB(252, 219, 192) Then
                curCell.Interior.Color = RGB(210, 231, 197)
            End If
            If curCell.Interior.Color = 2327285 Then  'RGB(252, 219, 192) Then
                curCell.Interior.Color = RGB(96, 148, 61)
            End If
            'If Abs(curCell.Value) < 0.01 Then curCell.Value = 0
        Next Col
    Next Counter
    For Counter = 1 To 300
        For Col = 1 To 50
            Set curCell = Worksheets("Expenses").Cells(Counter, Col)
            If curCell.Interior.Color = 12639228 Then  'RGB(252, 219, 192) Then
                curCell.Interior.Color = RGB(210, 231, 197)
            End If
            If curCell.Interior.Color = 2327285 Then  'RGB(252, 219, 192) Then
                curCell.Interior.Color = RGB(96, 148, 61)
            End If
            'If Abs(curCell.Value) < 0.01 Then curCell.Value = 0
        Next Col
    Next Counter
    For Counter = 1 To 300
        For Col = 1 To 50
            Set curCell = Worksheets("IncomeDeductions").Cells(Counter, Col)
            If curCell.Interior.Color = 12639228 Then  'RGB(252, 219, 192) Then
                curCell.Interior.Color = RGB(210, 231, 197)
            End If
            If curCell.Interior.Color = 2327285 Then  'RGB(252, 219, 192) Then
                curCell.Interior.Color = RGB(96, 148, 61)
            End If
            'If Abs(curCell.Value) < 0.01 Then curCell.Value = 0
        Next Col
    Next Counter
    For Counter = 1 To 300
        For Col = 1 To 50
            Set curCell = Worksheets("TurnoverRatios").Cells(Counter, Col)
            If curCell.Interior.Color = 12639228 Then  'RGB(252, 219, 192) Then
                curCell.Interior.Color = RGB(210, 231, 197)
            End If
            If curCell.Interior.Color = 2327285 Then  'RGB(252, 219, 192) Then
                curCell.Interior.Color = RGB(96, 148, 61)
            End If
            'If Abs(curCell.Value) < 0.01 Then curCell.Value = 0
        Next Col
    Next Counter
    For Counter = 1 To 300
        For Col = 1 To 50
            Set curCell = Worksheets("EquipmentDetailUsed").Cells(Counter, Col)
            If curCell.Interior.Color = 12639228 Then  'RGB(252, 219, 192) Then
                curCell.Interior.Color = RGB(210, 231, 197)
            End If
            If curCell.Interior.Color = 2327285 Then  'RGB(252, 219, 192) Then
                curCell.Interior.Color = RGB(96, 148, 61)
            End If
            'If Abs(curCell.Value) < 0.01 Then curCell.Value = 0
        Next Col
    Next Counter

    MsgBox "Completed"
End Sub




No comments:

Post a Comment