Macro Type Error

[SteveNBethesda]

I just upgraded to Office 365 and now one of my macros is generating a Type Mismatch error. The macro is pasted below, and the offending code is highlighted in red, bold italics. I would greatly appreciate any help. Thanks in advance.

Sub ColorRows2()
'
' ColorRows Macro
' Macro recorded 1/17/2020 by Steven Lapidus
'
Dim RowDef As String
Dim Processed As String
Dim Paid As String
Dim I As Integer
Dim ClaimsNo As Integer

Range("O1").Select 'Select Cell for Count Formula
ActiveCell.FormulaR1C1 = "=COUNTA(R[1]C[-14]:R[299]C[-14])" 'Count Data Rows
ClaimsNo = ActiveCell.Value 'Save Number of Claims
Range("A2:M300").Select 'Select All Data Rows in Array
Selection.Interior.ColorIndex = xlNone 'Color all rows white (no fill)

'Response = MsgBox(Counter)

I = 1
Application.ScreenUpdating = False 'Turn off screen refresh
Do While I <= ClaimsNo
I = I + 1 'Increment row counter
RowDef = "A" & I & ":M" & I 'Define current row
Processed = Range("F" & I) <> Blank 'If Adjusted then Processed
NotProcessed = Range("F" & I) = Blank 'If not Adjusted then Not Processed
Paid = Range("J" & I) <> Blank 'If Date Paid then Paid
NotPaid = Range("J" & I) = Blank 'If not Date Paid then Not Paid
'Response = MsgBox(I & Processed)
'Response = MsgBox(I & Paid)


If NotProcessed Then 'If not processed by insurance company
Range(RowDef).Select 'Select row
Selection.Interior.ColorIndex = xlNone 'Color row white (no fill)
ElseIf Processed And NotPaid Then 'If processed but not paid
Range(RowDef).Select 'Select row
With Selection.Interior
.ColorIndex = 36 'Color row yellow
.Pattern = xlSolid
End With
Else 'If processed and paid
Range(RowDef).Select 'Select row
With Selection.Interior
.ColorIndex = 35 'Color row grey
.Pattern = xlSolid
End With
End If
Loop

Call SortExpenses

Application.ScreenUpdating = True

End Sub

[#2]

I think you can forgo the VBA and just use conditional formatting for this.

Nevertheless, if the VBA is important, then I would recommend a couple of things to straighten this out:

1) add Option Explicit to the top of the module - this will force you to declare all variables and reduce the likelihood of type errors, or at least increase your chances of avoiding them in the first place

2) dim processed as boolean

3) dim paid as boolean

4) Don't use separate variables for NotProcessed and NotPaid. If you dim processed and paid as boolean, you can just:

If processed and Not paid Then

... etc

[#3]

@flexyourdata 

Thank you for your excellent suggestions! I hadn't thought about Conditional Formatting, but that's a great recommendation.

Declaring variables in one location is also a good habit, in fact, back in the day when I was a programmer, that was always my style and recommendation. However, I'm not a VBA programmer and typically fly by the seat of my pants! With that said, I changed all my variables to "variant" and that seems to work.

Thanks again for your comments and suggestions!!!

[#4]

A few more suggestions:
never use .select, selection. and ActiveCell. if you can avoid it. Just use the cell reference.
as for the line with an issue you don't even need the AND as the first IF statement is IF NOTPROCESSED so therefore the ELSEIF must have PROCESSED = TRUE so just drop that and use ELSEIF NOTPAID.
As already noted you can drop the extra PROCESSED & PAID variables
But most importantly, as suggested, just use conditional formatting.

[#5]

Thanks for the additional suggestions. I plan to create a copy of my spreadsheet and experiment with all the suggested REPLIES.

Have a great day!!!