I guess I can mark this thread as resolved. Thank you for your help, again.
Posts by coldturkey
-
-
Thank you Rory, I added Application. onto the range and the macro now works perfectly.
-
It's got some confidential information in it.
What I will try and do is remove most of the information in it and then post a barebones copy of it. -
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?
Display MoreCode- 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 failedThe 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.