Compile Error: Next Without For Error - Need Help Please :)

[tatertot]

I am using the code below to find any cells in the designated range that have "yes" or "no". In doing so, i was wanting to supply some special formatting for each instance.

Issue is I am receiving a "Compile Error: Next Without for Error". Could anyone tell me what I am doing wrong to resolve this issue??

Sub ValidateAuditData()

'Create macro to validate the input data


Dim AuditRange As Range
Set AuditRange = Worksheets("Sheet1").Range("A6:D199")


AuditRange.SpecialCells(xlCellTypeBlanks).Interior.ColorIndex = 6


'Create code to remove fill


For Each cell In AuditRange


If cell.Value = "Yes" Then
    cell.Interior.ColorIndex = 4
    cell.Font.Bold
If cell.Value = "No" Then
    cell.Interior.ColorIndex = 3
    cell.Font.Bold
    Next


End Sub

 

[alansidman]

Add two End if Statements as below

If cell.Value = "Yes" Then
    cell.Interior.ColorIndex = 4
    cell.Font.Bold
If cell.Value = "No" Then
    cell.Interior.ColorIndex = 3
    cell
End if
End if
Next

 

[tatertot]

Hi Alan,

Thanks for the suggestion! When I added the code to my macro I get the following error:

"Run-time error'1004: Bold Method of font class failed"

Suggestion for correcting?

 

[alansidman]

try this modification of your code

Option Explicit


Sub ValidateAuditData()
Dim cell As Range
'Create macro to validate the input data




Dim AuditRange As Range
Set AuditRange = Worksheets("Sheet1").Range("A6:D16")




'AuditRange.SpecialCells(xlCellTypeBlanks).Interior.ColorIndex = 6




'Create code to remove fill




For Each cell In AuditRange




If cell.Value = "Yes" Then
    cell.Interior.ColorIndex = 4
    cell.Font.Bold = True
ElseIf cell.Value = "No" Then
    cell.Interior.ColorIndex = 3
    cell.Font.Bold = True
End If
    Next


End Sub

 

[tatertot]

Thanks Alan!