mismatch error when populate date in adjacent cell based on word in another

[leap out]

Hello
I search for way to fix mismatch error in this line

If Range("E2:E" & I).Value = "PAID"

after populate date in adjacent cells in column F if the cells in column E contain "PAID" will show the error !
I use this

Dim I As Long, LR As Long
For I = 2 To LR
If Range("B2:B" & I).Interior.ColorIndex = xlNone Then Range("E2:E" & I) = "PAID"
If Range("E2:E" & I).Value = "PAID" Then Range("F2:F" & I) = Date
Next I

but I'm not sure where is my bad !

 

[Joe4]

Where exactly are you defining the value of "LR"?
If you have not defined it anywhere, it has a value of 0, so you are literally telling your loop to go:

For I = 2 to 0

and row 0 is not possible and will result in an error.

In code like you have posted above, people will typically calculate and set LR before using it in a loop, i.e.

Dim I As Long, LR As Long
LR = Cells(Rows.Count, "F").End(xlUp).Row
For I = 2 To LR
    If Range("B2:B" & I).Interior.ColorIndex = xlNone Then Range("E2:E" & I) = "PAID"
    If Range("E2:E" & I).Value = "PAID" Then Range("F2:F" & I) = Date
Next I

 

[leap out]

thanks
nothing show !

 

[Joe4]

Sorry, I misread your original question. It looks like you should be using column E to find the last row with data instead of column F:

Dim I As Long, LR As Long
LR = Cells(Rows.Count, "E").End(xlUp).Row
For I = 2 To LR
    If Range("B2:B" & I).Interior.ColorIndex = xlNone Then Range("E2:E" & I) = "PAID"
    If Range("E2:E" & I).Value = "PAID" Then Range("F2:F" & I) = Date
Next I

 

[leap out]

it shows the same error and in the same line as I have ever mentioned.

 

[Alex Blakenburg]

The if statement is not happy with trying to evaluate a range of cells.

Sub test()

Dim I As Long, LR As Long
LR = Cells(Rows.Count, "E").End(xlUp).Row
For I = 2 To LR
    If Range("B" & I).Interior.ColorIndex = xlNone Then Range("E" & I) = "PAID"
    If Range("E" & I).Value = "PAID" Then Range("F" & I) = Date
Next I

End Sub

 

[leap out]

I don't understand what you try to say !
I'm surprised from the problem as bold ranges , I no know why !

If Range("B2:B" & I).Interior.ColorIndex = xlNone Then Range("E2:E" & I) = "PAID"
    If Range("E2:E" & I).Value = "PAID" Then Range("F2:F" & I) = Date

I would inform you also when change as bold red

Range("E2:E" & I).Text= "PAID"

also works perfectly
anyway your updating has solved my problem.
thank you so much .

 

[Alex Blakenburg]

Interesting.
See if this helps:

20230926 VBA If Range of cells leap out.xlsm
	A	B	C
2	Expression	No of Cells	Result
3	Range("E2").Value = "PAID"	1	Works
4	Range("E2:E2").Value = "PAID"	1	Works
5	Range("E2:E3").Value = "PAID"	2	Error 13 Type mismatch
6	Range("E2:E4").Value = "PAID"	3	Error 13 Type mismatch
7	Range("E2:E4").Text = "PAID"	3	The Text part only returns a value if all 3 cells have the exact same value, otherwise returns null
Notes

 

[leap out]

case 1 doesn't show any thing
case 2: case 4 will show error
final case I don't understand it !

f all 3 cells have the exact same value

you're talking about cells in column C,D,E ?

 

[Alex Blakenburg]

you're talking about cells in column C,D,E ?

No the If statement was using a range of E2:E & I.
So below is what your Text option returns when I =4 and the Range is Range("E2:E4").
Unless all 3 Cells in the Range E2:E4 are the same, the Text property returns Null.