• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

identify color in cell

S P P

Member
You can identify if there is color in certain cells as in the attached example.
 

Attachments

  • SPP identify color in cell.xlsx
    9.2 KB · Views: 5
S P P
Something like this =is_color(J5)
... r is Your named range
Code:
Function is_color(r)
    co = "no color"
    For Each c In Range(r)
        If c.Interior.ColorIndex <> xlNone Then co = "color"
    Next c
    is_color = co
End Function
 
vletm
Thanks again for your collaboration.

B1 to G1 color, no color

A2 to A6 color, no color

J5 to K8 was a mode that I tried to explain how I intend
 
I got it this way

I need to update automatically as soon as I change color.
 

Attachments

  • SPP identify color in cell1.xlsm
    15.5 KB · Views: 1
S P P
I did as You tried to explain
... but that wasn't - what did You want ...
For me Function works after You've changed its value ... not it's color.
This version uses ranges - not as in previous sample.
Code:
Function is_color(r)
    co = "no color"
    For Each c In r
        If c.Interior.ColorIndex <> xlNone Then co = "color"
    Next c
    is_color = co
End Function
It could possible to show those results when You're selecting cell ... but still it won't work as You ... need.
... or ...
You should change those colors ... how do You need to change those colors?
 
If you wish to determine the color code for a specific cell then this should work

Code:
Sub FindColor()
MsgBox "Background " & Selection.Interior.ColorIndex
MsgBox "Font Color " & Selection.Font.ColorIndex
End Sub
 
Debaser

Thanks for the information.

It only recalculates if you click on the formula.

It is unfeasible
 
S P P
Did You read my the last sentence?
You should able to explain - what do You really need?
.. and eg Could You forget any formula clicking.
 
vletm

B2:G6 cells color 3 does not always stay in the same cells

Not even the way I did it doesn't work. Color 3 and coming from formatting.
 
S P P
Wasn't Your wish to know - is there color in some cell?
... no which matter of color
... or any custom formatting.
Did You read - how do You need to change those colors?
If You could able to explain
- what do You really need?
as well as
How would You use this?
... then it would be possible to try to find solution for You.
 
S P P
I did one sample for You ... I had to guess few features.
If Your cell's color change then You'll select that cell ... then this way.
You could also see number of cells which has color in cell.
I would show those numbers instead almost same texts.
If You select cell A1 then all will clear.
You could do selection as large range as You would like to use.
 

Attachments

  • S P P.xlsb
    17.2 KB · Views: 4
Back
Top