CODEWITHSUNDEEP
vbaexcelexcel-2010
fantomknight1
fantomknight1

Reputation: 21

Excel 2010 VBA Code not running

I'm currently using Excel 2010 and am trying to run some code I put together in VBA for Applications (after hitting alt+F11). I typed up the code in a notepad that appeared after double clicking the project I wanted to work on. I also saved everything as Excel Macro Enabled Workbook (*.xlsm).

I am trying to color the backgrounds of Column D either green or red if columns S, T, and U meet the criteria. If the columns all have a value of 0 then Cell D should be colored green. If not, it should be colored red. 

Sub GreenOrRed()
  Dim i As Integer
  For i = 2 To i = 27293
    If (Cells(i, "S").Value = 0 And Cells(i, "T").Value = 0 And Cells(i, "U").Value = 0) Then
        Cells(i, "D").Interior.ColorIndex = 10
    Else
        Cells(i, "D").Interior.ColorIndex = 9
    End If
   Next i
End Sub

The code runs and doesn't throw any error but it also doesn't do anything. What am I doing wrong?

Upvotes: 1

Views: 341

Answers (4)

user4039065
user4039065

Reputation:

You might consider setting one (or two) conditional formatting rules.

Option Explicit

Sub GreenOrRed()
    With ActiveSheet
        With .Range(.Cells(2, "D"), .Cells(.Rows.Count, "D").End(xlUp))
            .Interior.ColorIndex = 9
            .FormatConditions.Delete
            With .FormatConditions.Add(Type:=xlExpression, Formula1:="=and(sum($S2)=0, sum($T2)=0, sum($U2)=0)")
                .Interior.ColorIndex = 10
                .StopIfTrue = True
            End With
        End With
    End With
End Sub

I've used individual SUM functions to ensure that any text returns a numerical value of zero.

Alternate AutoFilter method.

Sub GreenOrRedFiltered()
    With ActiveSheet
        If .AutoFilterMode Then .AutoFilterMode = False
        With .Range(.Cells(1, "D"), .Cells(.Rows.Count, "D").End(xlUp)).Resize(, 18)
            .Resize(.Rows.Count - 1, .Columns.Count).Offset(1, 0).Columns(1).Interior.ColorIndex = 9
            .AutoFilter Field:=16, Criteria1:=0, Operator:=xlOr, Criteria2:=vbNullString
            .AutoFilter Field:=17, Criteria1:=0, Operator:=xlOr, Criteria2:=vbNullString
            .AutoFilter Field:=18, Criteria1:=0, Operator:=xlOr, Criteria2:=vbNullString
            With .Resize(.Rows.Count - 1, .Columns.Count).Offset(1, 0)
                If CBool(Application.Subtotal(103, .Cells)) Then
                    .Columns(1).SpecialCells(xlCellTypeVisible).Interior.ColorIndex = 10
                End If
            End With
        End With
        If .AutoFilterMode Then .AutoFilterMode = False
    End With
End Sub

Upvotes: 0

Kostas K.
Kostas K.

Reputation: 8518

A slightly different approach:

Sub GreenOrRed()
    Dim r As Range, rr As Range
    Set rr = Range("D1:D27293")

    For Each r In rr
        If r.Offset(0, 15).Value = 0 And r.Offset(0, 16).Value = 0 And r.Offset(0, 17).Value = 0 Then
            r.Interior.ColorIndex = 10
        Else
            r.Interior.ColorIndex = 9
        End If
    Next r
End Sub

Upvotes: 1

India.Rocket
India.Rocket

Reputation: 1245

Changed For condition. Try this:-

Sub GreenOrRed()
  Dim i As Integer
  For i = 2 To 27293
    If (Cells(i, "S").Value = 0 And Cells(i, "T").Value = 0 And Cells(i, "U").Value = 0) Then
        Cells(i, "D").Interior.ColorIndex = 10
    Else
        Cells(i, "D").Interior.ColorIndex = 9
    End If
   Next i
End Sub

Upvotes: 1

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9976

You are using counter in For loop incorrectly. It should be like this...

For i = 2 To 27293

Upvotes: 2

Related Questions