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.