Problem using "Cells" in "Range" "400" error

I am having problems referrring to a range using .Cells

If I try to use the remarked code (red text), it crashes with VBA "400" error for which I cannot find any explanation. It also crashes with same "400" message if I use a cells reference to a numeric column instead of "y").

The code works using the black code: it loops a column in Sheet("Holidays") and loops a row in Sheet("Schedule"), then color fills 2 ranges in columns in Sheet("Schedule") when the values in 2 cells are equal.



I have attached a scaled down version of my Workbook with this code.

What am I doing wrong?

Thanks,

Please Login or Register  to view this content.

Hello johnny winter,

When working with Cells you must remember if they are not qualified with a prefix then VBA assumes the cells are on the ActiveSheet. I have changed your code to use the With statement to ensure the cells belong to the correct worksheet.

Please Login or Register  to view this content.