When 'Worksheet_Change' event doesn't work..

[DonyBoy]

I’m looking to activate some VB code when the user changes the font colour in a cell. Worksheet_Change doesn’t seem to work. Any ideas?

 

[xlbo]

that is because nothing has physically changed when you alter the format. Worksheet change is triggered by a physical change to the worksheet - ie the entering or removing of data. There is no change event associated with altering formats

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[DonyBoy]

Thanks Geoff, but is there a clever way around this? Basically I have some names in column-B. When a user changes the font colour for a name, I want to colour some cells in that SAME ROW by the same colour of that font.

for example, if the user changes the colour font in cell B8 to blue, I want to colour D8:F8 with blue.

 

[xlbo]

The only event you might be able to hook into the the worksheet CALCULATE event. Even then, I doubt it will work - this is the danger of attempting to use colour to define what you are doing rather than highlight what has been done.

Rather than get a user to change the font colour of a name, you would be better off asking them to update a flag next to the name - you can then have VBA or conditional formatting do all your colouring for you based on the flag.

Basically - don't rely on colours to do anything - use data and then apply colour to highlight the data

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[CBasicAsslember]

Use a sheet variable to store the previous selected range and check the range for format changes.

Option Explicit
Dim sLastAddress As String

Private Sub Worksheet_Activate()
Application.EnableEvents = False
'Start the user in the same cell
Me.Range("A1").Select
sLastAddress = Me.Range("A1").Address
Application.EnableEvents = True

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim iColor
   
MsgBox "You just left cell " & sLastAddress & _
Range(sLastAddress).Font.ColorIndex
   
   'Put code here

' Save the address of the range your in
sLastAddress = Target.Address

End Sub

 

[xlbo]

Personally, I dislike the selection change event - very high processing overheads for what you want to achieve.

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question