When I run the following macro as a normal sub it works fine but when I convert it into a worksheet_change sub it gives me an error on the For Each line.
Can somebody please explain why and show me how to fix it?
Private Sub Worksheet_Change(ByVal Target As Range) ' Sub to copy format from Carer_initials to activecell if activecell is the same value Dim c As Range If ActiveCell.Value <> "" Then For Each c In Range("Carer_initials") If ActiveCell.Value = c.Value Then ActiveCell.font.color = c.font.color Exit For End If Next c End If End Sub
The error message I received is:
Run-time error '1004'
Method' Range of object'_Worksheet failed
The active cell has a drop-down list in it and I would like to colour the cell depending on the choice made. Unfortunately you can't pass through formatting when you use a drop-down list.
My use of VBA is pretty much self-taught so I always have trouble figuring out the correct syntax of some of these.