My VBA keeps giving a next without for error... What am I missing. Brain fried today..

I

ILMWT

New Member
Joined
Dec 9, 2014
Messages
35
Sub Format()


Dim i As Long


For i = 5 To 99 ' Can edit 99 to the maximum row number needed


If ActiveSheet.Cells(i, 3).Value <> "" Then 'Referencing difference between column N and C. We want N to be less than or equal to 7 days from C.


If ActiveSheet.Cells(i, 14).Value = "" Then


ActiveSheet.Cells(i, 14).Interior.Color = 12632256


ElseIf Application.WorksheetFunction.NetworkDays(Cells(i, 3), Cells(i, 14)) <= 7 Then


ActiveSheet.Cells(i, 14).Interior.Color = vbGreen


ElseIf Application.WorksheetFunction.NetworkDays(Cells(i, 3), Cells(i, 14)) >= 8 Then


ActiveSheet.Cells(i, 14).Interior.Color = vbRed


End If


If ActiveSheet.Cells(i, 3).Value <> "" Then 'Referencing difference between column Q and N. We want Q to be less than or equal to 2 days from Q.


If ActiveSheet.Cells(i, 17).Value = "" Then

ActiveSheet.Cells(i, 17).Interior.Color = 12632256


ElseIf Application.WorksheetFunction.NetworkDays(Cells(i, 14), Cells(i, 17)) <= 2 Then


ActiveSheet.Cells(i, 17).Interior.Color = vbGreen

ElseIf Application.WorksheetFunction.NetworkDays(Cells(i, 14), Cells(i, 17)) >= 2 Then


ActiveSheet.Cells(i, 17).Interior.Color = vbRed

End If

If ActiveSheet.Cells(i, 3).Value <> "" Then 'Referencing difference between column R and Q. We want R to be less than or equal to 2 days from Q.


If ActiveSheet.Cells(i, 18).Value = "" Then


ActiveSheet.Cells(i, 18).Interior.Color = 12632256


ElseIf Application.WorksheetFunction.NetworkDays(Cells(i, 17), Cells(i, 18)) <= 2 Then


ActiveSheet.Cells(i, 18).Interior.Color = vbGreen


ElseIf Application.WorksheetFunction.NetworkDays(Cells(i, 17), Cells(i, 18)) >= 3 Then


ActiveSheet.Cells(i, 14).Interior.Color = vbRed

End If

If ActiveSheet.Cells(i, 3).Value <> "" Then 'Referencing difference between column S and R. We want S to be less than or equal to 2 days from R.


If ActiveSheet.Cells(i, 19).Value = "" Then


ActiveSheet.Cells(i, 19).Interior.Color = 12632256


ElseIf Application.WorksheetFunction.NetworkDays(Cells(i, 18), Cells(i, 19)) <= 2 Then


ActiveSheet.Cells(i, 19).Interior.Color = vbGreen


ElseIf Application.WorksheetFunction.NetworkDays(Cells(i, 18), Cells(i, 19)) >= 3 Then


ActiveSheet.Cells(i, 19).Interior.Color = vbRed

End If

If ActiveSheet.Cells(i, 3).Value <> "" Then 'Referencing difference between column V and S. We want V to be less than or equal to 2 days from S.


If ActiveSheet.Cells(i, 22).Value = "" Then


ActiveSheet.Cells(i, 22).Interior.Color = 12632256


ElseIf Application.WorksheetFunction.NetworkDays(Cells(i, 19), Cells(i, 22)) <= 2 Then


ActiveSheet.Cells(i, 22).Interior.Color = vbGreen


ElseIf Application.WorksheetFunction.NetworkDays(Cells(i, 19), Cells(i, 22)) >= 3 Then

ActiveSheet.Cells(i, 22).Interior.Color = vbRed


End If


Next i



End Sub
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
Actually you are missing multiple End Ifs. If you indent your code you should spot that:

Rich (BB code): 
Sub Format()
    Dim i As Long
    For i = 5 To 99 ' Can edit 99 to the maximum row number needed
        If ActiveSheet.Cells(i, 3).Value <> "" Then 'Referencing difference between column N and C. We want N to be less than or equal to 7 days from C.
            If ActiveSheet.Cells(i, 14).Value = "" Then
                ActiveSheet.Cells(i, 14).Interior.Color = 12632256
            ElseIf Application.WorksheetFunction.NetworkDays(Cells(i, 3), Cells(i, 14)) <= 7 Then
                ActiveSheet.Cells(i, 14).Interior.Color = vbGreen
            ElseIf Application.WorksheetFunction.NetworkDays(Cells(i, 3), Cells(i, 14)) >= 8 Then
                ActiveSheet.Cells(i, 14).Interior.Color = vbRed
            End If
        End If
        If ActiveSheet.Cells(i, 3).Value <> "" Then 'Referencing difference between column Q and N. We want Q to be less than or equal to 2 days from Q.
            If ActiveSheet.Cells(i, 17).Value = "" Then
                ActiveSheet.Cells(i, 17).Interior.Color = 12632256
            ElseIf Application.WorksheetFunction.NetworkDays(Cells(i, 14), Cells(i, 17)) <= 2 Then
                ActiveSheet.Cells(i, 17).Interior.Color = vbGreen
            ElseIf Application.WorksheetFunction.NetworkDays(Cells(i, 14), Cells(i, 17)) >= 2 Then
                ActiveSheet.Cells(i, 17).Interior.Color = vbRed
            End If
        End If
        If ActiveSheet.Cells(i, 3).Value <> "" Then 'Referencing difference between column R and Q. We want R to be less than or equal to 2 days from Q.
            If ActiveSheet.Cells(i, 18).Value = "" Then
                ActiveSheet.Cells(i, 18).Interior.Color = 12632256
            ElseIf Application.WorksheetFunction.NetworkDays(Cells(i, 17), Cells(i, 18)) <= 2 Then
                ActiveSheet.Cells(i, 18).Interior.Color = vbGreen
            ElseIf Application.WorksheetFunction.NetworkDays(Cells(i, 17), Cells(i, 18)) >= 3 Then
                ActiveSheet.Cells(i, 14).Interior.Color = vbRed
            End If
        End If
        If ActiveSheet.Cells(i, 3).Value <> "" Then 'Referencing difference between column S and R. We want S to be less than or equal to 2 days from R.
            If ActiveSheet.Cells(i, 19).Value = "" Then
                ActiveSheet.Cells(i, 19).Interior.Color = 12632256
            ElseIf Application.WorksheetFunction.NetworkDays(Cells(i, 18), Cells(i, 19)) <= 2 Then
                ActiveSheet.Cells(i, 19).Interior.Color = vbGreen
            ElseIf Application.WorksheetFunction.NetworkDays(Cells(i, 18), Cells(i, 19)) >= 3 Then
                ActiveSheet.Cells(i, 19).Interior.Color = vbRed
            End If
        End If
        If ActiveSheet.Cells(i, 3).Value <> "" Then 'Referencing difference between column V and S. We want V to be less than or equal to 2 days from S.
            If ActiveSheet.Cells(i, 22).Value = "" Then
                ActiveSheet.Cells(i, 22).Interior.Color = 12632256
            ElseIf Application.WorksheetFunction.NetworkDays(Cells(i, 19), Cells(i, 22)) <= 2 Then
                ActiveSheet.Cells(i, 22).Interior.Color = vbGreen
            ElseIf Application.WorksheetFunction.NetworkDays(Cells(i, 19), Cells(i, 22)) >= 3 Then
                ActiveSheet.Cells(i, 22).Interior.Color = vbRed
            End If
        End If
    Next i
End Sub
 
Upvote 0
Thank you!! Thought I am getting a new error now.

Run-Time '1004':

Unable to get the NetworkDays property of the Worksheet FunctionClass

Then it highlights this elseif "ElseIf Application.WorksheetFunction.NetworkDays(Cells(i, 14), Cells(i, 17)) <= 2 Then"
Which is part of the second if statement referencing Q and N. What am I missing there?
 
Upvote 0
Maybe this UNTESTED
Code: 
Sub Format()
Dim i As Long
For i = 5 To 99 ' Can edit 99 to the maximum row number needed
If Cells(i, 3).Value <> "" Then 'Referencing difference between column N and C. We want N to be less than or equal to 7 days from C.
    If Cells(i, 14).Value = "" Then
    Cells(i, 14).Interior.Color = 12632256
    ElseIf Application.WorksheetFunction.NetworkDays(Cells(i, 3), Cells(i, 14)) <= 7 Then
    Cells(i, 14).Interior.Color = vbGreen
    ElseIf Application.WorksheetFunction.NetworkDays(Cells(i, 3), Cells(i, 14)) >= 8 Then
    Cells(i, 14).Interior.Color = vbRed
    End If
    If Cells(i, 17).Value = "" Then
    Cells(i, 17).Interior.Color = 12632256
    ElseIf Application.WorksheetFunction.NetworkDays(Cells(i, 14), Cells(i, 17)) <= 2 Then
    Cells(i, 17).Interior.Color = vbGreen
    ElseIf Application.WorksheetFunction.NetworkDays(Cells(i, 14), Cells(i, 17)) >= 2 Then
    Cells(i, 17).Interior.Color = vbRed
    End If
    If Cells(i, 18).Value = "" Then
    Cells(i, 18).Interior.Color = 12632256
    ElseIf Application.WorksheetFunction.NetworkDays(Cells(i, 17), Cells(i, 18)) <= 2 Then
    Cells(i, 18).Interior.Color = vbGreen
    ElseIf Application.WorksheetFunction.NetworkDays(Cells(i, 17), Cells(i, 18)) >= 3 Then
    Cells(i, 14).Interior.Color = vbRed
    End If
    If Cells(i, 19).Value = "" Then
    Cells(i, 19).Interior.Color = 12632256
    ElseIf Application.WorksheetFunction.NetworkDays(Cells(i, 18), Cells(i, 19)) <= 2 Then
    Cells(i, 19).Interior.Color = vbGreen
    ElseIf Application.WorksheetFunction.NetworkDays(Cells(i, 18), Cells(i, 19)) >= 3 Then
    Cells(i, 19).Interior.Color = vbRed
    End If
    If Cells(i, 22).Value = "" Then
    Cells(i, 22).Interior.Color = 12632256
    ElseIf Application.WorksheetFunction.NetworkDays(Cells(i, 19), Cells(i, 22)) <= 2 Then
    Cells(i, 22).Interior.Color = vbGreen
    ElseIf Application.WorksheetFunction.NetworkDays(Cells(i, 19), Cells(i, 22)) >= 3 Then
    Cells(i, 22).Interior.Color = vbRed
    End If
End If
Next i
End Sub
 
Upvote 0
Thank you for the help, though this one is yielding the same run-time error as the prior comment I had made. Any ideas on why this is now happening?
 
Upvote 0
Andrew Poulsom said:
What's in those cells when you get the error?
Click to expand...

They should all be dates that are formatted as short dates. Though one of them had text in it. "N/A" with a strike through. I tried it on a different tab with only short dates and still had the issue.

EDIT: Scratch that. Re-tried after closing, re-opening and removing the text. Worked beautifully. Thank you so much for the help. Michael M as well!!!
 
Upvote 0
One more question.... If you have the time. What would I add in at the end to say, if a cell in column 3 is populated but the cell in 23 is blank, then grey. If it has the letter Y in it, then green. If it has the letter N in it, then red?

Like:

If ActiveSheet.Cells(i, 3).Value <> "" Then
If ActiveSheet.Cells(i, 23).Value = "" Then
ActiveSheet.Cells(i, 23).Interior.Color = 12632256
ElseIf Application.Formula.If(Cells(i, 23)) = Y Then
ActiveSheet.Cells(i, 23).Interior.Color = vbGreen
ElseIf Application.WorksheetFunction.If(Cells(i, 23)) = N Then
ActiveSheet.Cells(i, 23).Interior.Color = vbRed

I think this should work?
If ActiveSheet.Cells(i, 3).Value <> "" Then 'Referencing difference between column V and S. We want V to be less than or equal to 2 days from S.
If ActiveSheet.Cells(i, 23).Value = "" Then
ActiveSheet.Cells(i, 23).Interior.Color = 12632256
ElseIf ActiveSheet.Cells(i, 23) = "Y" Then
ActiveSheet.Cells(i, 23).Interior.Color = vbGreen
ElseIf ActiveSheet.Cells(i, 23) = "N" Then
ActiveSheet.Cells(i, 23).Interior.Color = vbRed
End If
End If
 
Last edited:
Upvote 0
Code: 
If Not IsEmpty(ActiveSheet.Cells(i, 3)) Then
    If IsEmpty(ActiveSheet.Cells(i, 23)) Then
        ActiveSheet.Cells(i, 23).Interior.Color = 12632256
    ElseIf ActiveSheet.Cells(i, 23) = "Y" Then
        ActiveSheet.Cells(i, 23).Interior.Color = vbGreen
    ElseIf ActiveSheet.Cells(i, 23) = "N" Then
        ActiveSheet.Cells(i, 23).Interior.Color = vbRed
    End If
End If
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
VBA script delete row containing specific text
Replies
1
Views
542
DanteAmor
DanteAmor
E
  • Question
Help Cleaning Up / Optimizing VBA Code
Replies
6
Views
265
offthelip
O
A
  • Question
Code to Add Data From UserForm In Row Above or Below Based on Criteria
Replies
1
Views
190
amoverton2
A
WildBurrow
  • Question
Copy/Paste data to next empty row within a range - WITHOUT shifting subsequent rows up
Replies
0
Views
100
WildBurrow
WildBurrow
S
  • Solved
Next without For
Replies
2
Views
163
skull_eagle
S

Forum statistics

Threads
1,214,376
Messages
6,119,172
Members
448,870
Latest member
max_pedreira

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top