Cell Colour

[Simont485]

I have found VBA code from another thread that returns a value for a cell colour. But it will only update when F9 is pressed for a cell colour change

In B1 =GetColor(A1) when colour in A1 changes B1 output stays the same

CODE:

Function GetColor(r As Range) As Integer
Application.Volatile
GetColor = r.Interior.ColorIndex
End Function


Is it possible to have this update automatically?

 

[Yongle]

There is no defined Event that is triggered when the format of a cell changes

But there may still be a way "create" one based on something else that happens at the same time

How is the colour changed? Do you simply select the cell and manually change it?

 

[Simont485]

Yes its a manual change so nothing else happens at the same time......

 

[Yongle]

It is not possible to detect the change of colour unless user "does" something else afterwards

How about one of these?

Option 1
- amend cell colour and double-click in cell A1 to update the formula

Place this in the SHEET module (not a standard module)

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Address = "$A$1" Then
        Cancel = True
        [A1].Calculate
    End If
End Sub

Option 2
- amend cell colour and click on any other cell to update the formula

Place this in the SHEET module (not a standard module)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address <> "$A$1" Then [A1].Calculate
End Sub

 

[Simont485]

Option 2 is perfect for what I'm trying to achieve!!

Many thanks

 

[Yongle]

You could narrow down the range of cells to only those immediately surrounding A1 to prevent the calculation taking place whenever any cell in the worksheet is selected.
Let us know if you need help doing that.