[Solved] ColorIn Function

[Sam]

I just tried to utilize this function and it works great, The only problem is that if I change the color of a cell the function doesn't auto update. How can i get it to auto update?

I've created a worksheet where I'm using color to indicate if the cost of an item has been paid. If cell background color is yellow then it has not been paid if it isn't yellow then it has already been paid. My data is in a table and I'm using Excel 365

Thanks

 

[Yousuf Shovon]

I just tried to utilize this function and it works great, The only problem is that if I change the color of a cell the function doesn't auto update. How can i get it to auto update?

I've created a worksheet where I'm using color to indicate if the cost of an item has been paid. If cell background color is yellow then it has not been paid if it isn't yellow then it has already been paid. My data is in a table and I'm using Excel 365

Thanks

Hello Sam,

Welcome to ExcelDemy forum! I understand you wish to auto update the COLORLN function. Is this a VBA function? Or are you referring to a conditional formatting function? Please clarify.

And if it is a VBA function, please provide the VBA code. We will try to provide the best solution for you.

Best Regards,
Yousuf Shovon

 

[Sam]

Yousuf,
In one of the articles posted on the web by Exceldemy there is a function that is created in the Name Manager that looks like this
=GET.CELL(63,INDIRECT("rc[-1]",FALSE)) this works but I don't know how it works but it does, it just doesn't update.
So I found this macro also published by Excelemy that looks like this:
Function ColorIn(color As Range) As Integer
ColorIn = color.Interior.ColorIndex
End Function

If there is a better way to achieve my goal I'm interested in hearing about it.

Thank you for your help

 

[Yousuf Shovon]

Yousuf,
In one of the articles posted on the web by Exceldemy there is a function that is created in the Name Manager that looks like this
=GET.CELL(63,INDIRECT("rc[-1]",FALSE)) this works but I don't know how it works but it does, it just doesn't update.
So I found this macro also published by Excelemy that looks like this:
Function ColorIn(color As Range) As Integer
ColorIn = color.Interior.ColorIndex
End Function

If there is a better way to achieve my goal I'm interested in hearing about it.

Thank you for your help

Hello Sam,

Thanks for the thorough explanation. I have modified the provided code so that the color index automatically updates whenever the color of the cell changes. I have used Worksheet_Change event in VBA to do so.

The modified code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.UsedRange) Is Nothing Then
        On Error GoTo ErrorHandler
        
        Application.EnableEvents = False
        Dim cell As Range
        For Each cell In Target
            cell.Offset(0, 1).Value = ColorIn(cell)
        Next cell
        
    End If
    Exit Sub
    
ErrorHandler:
    Application.EnableEvents = True
End Sub

Function ColorIn(color As Range) As Integer
    ColorIn = color.Interior.ColorIndex
End Function

Now, if you change the cell color, the color index auto updates.

Note: The worksheet_change event works when there is a change in the workbook. This code works fine when I am copying a cell color and pasting it on the Colorln function cells. But I am afraid it does not work when I change the color using the Home tab ribbon. Perhaps, it does not include in worksheet_change events.

Let me know if it works for you so far. I have attached the workbook for a better understanding.

Best Regards.

 

[Sam]

Yousuf, thank you very much I will give this a try.