ColorIn Function (1 Viewer)

[Peter Quill]

Hello,

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

 

[CJ_London]

Never heard of a colorin function- please clarify. If it is a vba function then provide the code.

or perhaps you are referring to conditional formatting?

 

[The_Doc_Man]

Did a search, could NOT find the COLORIN function on three tries. The great Google brain keeps on saying "Did you mean "color in" (as two words). Did someone supply this function to you or perhaps did you find it in a forum somewhere? Or do you have an add-in package for Excel?

 

[Peter Quill]

Hello,
Thanks all for your responses.
Once I created a function 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 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.
Thanks in advance.

 

[theDBguy]

Just curious, have you tried using Conditional Formatting?

 

[Peter Quill]

Just curious, have you tried using Conditional Formatting?

Yes, it was my first option. I am actually figuring out other options particularly this method to solve this issue.

 

[GPGeorge]

Yes, it was my first option. I am actually figuring out other options particularly this method to solve this issue.

I have a long-standing tradition. If there is a simple, effective, consistent, native way to accomplish a task, I use it and don't waste a lot of time trying to write a wad of new VBA code to replicate it. This applies not only to Access, but to Excel, PowerPoint, and Word, as well.

 

[CJ_London]

All your colorin function is returning the color of the cell

 

[The_Doc_Man]

Not only that, but if the input range is not single-celled, you only pick the upper left corner cell, even if the range is "rainbow."

When you say your function doesn't update, here is the question: When you tell Excel to do a full recompute, does it update then? Because if it does, you are simply not triggering anything to cause a recompute to occur. Excel doesn't ALWAYS recompute everything. Sometimes it is more selective.

 

[arnelgp]

it could be easy as suggested in post#5.
can you post a sample xlsx/xlsm file with dummy data.

 

[Gasman]

Hello,
Thanks all for your responses.
Once I created a function 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 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.
Thanks in advance.

Nor do I
If anything I think all it would do is return the value of the fill colour?

I cannot even see where you call it.

Here is a line of code from a workbook of mine where I set it and fontcolor to a value stored in another sheet.

            .FormatConditions(wsCond.Cells(lngCounter, 8).Value).Font.Color = wsCond.Cells(lngCounter, 5).Value
            .FormatConditions(wsCond.Cells(lngCounter, 8).Value).Interior.Color = wsCond.Cells(lngCounter, 4).Value

 

[Isaac]

A part of me wants to +1 to dbGuy's suggestion. If conditional formatting will work, then it will do a thousand times more for you with 1/1000th of the work of reinventing the wheel. Make sure you examine its capabilities vs. your need thoroughly before discarding.

On the other hand, I've had many experiences where I started out thinking C.F. would be fine. After a while things got more complex. Then files were copied, re-made, auto-generated, etc., and it got harder and harder to maintain the C.F. paradigm - super complex and in some cases hard to re-create (think hours of manual work).

If I had another need arise these days, I would still consider CF, but also strongly consider a VBA-based, infinitely scale-able option like maintaining a cross-reference (config) worksheet with Rules. Columns like Type (contains, equal to, not, less than, etc), Rank (once this is hit exit the rest), Colors (I like simplicity of rgb), etc. The VBA function would very quickly apply ALL of these rules, and re-apply them ALL on demand. Editing them would be relatively easy compared to maintaining (and passing around, and copying, and building upon) a complex set of rules in the CF dialogue window.

 

[Gasman]

Which is pretty much what I do now for a partciular sheet, although perhaps a little simpler.

However be warned, I am having problems with this where sometimes it does not put the correct row number in the formula when being applied.
I have walked though the code and the data is as per column C, yet Excel changes it to a very high number just short of the max row number or just a few rows away.

 

[Isaac]

Which is pretty much what I do now for a partciular sheet, although perhaps a little simpler.

However be warned, I am having problems with this where sometimes it does not put the correct row number in the formula when being applied.
I have walked though the code and the data is as per column C, yet Excel changes it to a very high number just short of the max row number or just a few rows away.

View attachment 108574

That's very interesting, thanks for posting. Yes - something like that is exactly what I was imagining! And even better.

In your case I think you're probably using VBA to execute actual conditional formatting commands, right?
I was envisioning simply using VBA to set range colorindex interior etc. etc. etc.

I do like your way, a hybrid of homegrown + CF, but I have little to no experience with executing actual CF commands via VBA, so I can see where troubleshooting those CF formula-based rules could be tough. Your advantage is you're able to leverage Relativity without reinventing it explicitly all in code, which is nice, but also perhaps less documented for automation..

I can see how your way of doing it may tend to incur fairly complex layers of excel's "relative" brain-thinking ....... and possibly end up being hard to determine the reason.

What I was thinking of is a more literal, explicit applying of range colors ... which may avoid your occasional trouble-cases, but also likely be a bit slower.

It almost seems like perhaps you could test and switch to my more literal explicit method (no executing CF commands in VBA), without probably changing hardly anything about your config sheet.