Referencing format features

[sriche01]

Hello,

Just wondering if there is a function to reference format properties such as cell fill color or font color. We use both to indicate a time frame of review (e.g. dark blue means it was reviewed last in October). I was hoping I could reference the cell color that was used on one sheet to either apply the same color automatically on another sheet for the same item or give a text or numeric return that would indicate timing. I am stuck at the part of needing a function that references any format property.

Help!

 

[Joe4]

Not with native Excel functions, I believe. I think you would need to use VBA to do what you want.
How are these cells being formatted originally? Manually, or by Conditional Formatting.
If by Conditional Formatting, you may be able to use the same rule that colored the original cells to color these other cells too.

If colored manually, and you are open to a VBA solution, please provide specific details so we can help you (i.e. name of sheet involved, ranges involved, etc).

 

[sriche01]

Not with native Excel functions, I believe. I think you would need to use VBA to do what you want.
How are these cells being formatted originally? Manually, or by Conditional Formatting.
If by Conditional Formatting, you may be able to use the same rule that colored the original cells to color these other cells too.

It is manually done.

If colored manually, and you are open to a VBA solution, please provide specific details so we can help you (i.e. name of sheet involved, ranges involved, etc).

The sheet with the cells to reference is 'Sales Plan' and the ranges are M3:M447, R3:R447, W3:W447, AB3:AB447, AG3:AG447. Essentially these are the Sales Years for which we plan. 2019, 2020, 2021, 2022, and 2023 respectively.

I was wondering if we could create a vba function that pulls the fill color from a referenced cell???
Thanks!

 

[Joe4]

What is the other sheet involved?
How are the records on the two sheets linked together?

 

[sriche01]

What is the other sheet involved?
How are the records on the two sheets linked together?

Actually, they are not linked other than being similar lists of items within the same workbook. What I am looking for is a user made function with the basic syntax CellColor(lookup_cell) that would return a numeric reference to the fill color in the look up cell. I could nest that in a formula to determine how recently the item was reviewed. That is the extent of the VBA connection between the sheets that I want as this is a very formula driven spreadsheet.

Something like this: if(cellcolor(index(

#VALUE!

 

[Joe4]

What I am looking for is a user made function with the basic syntax CellColor(lookup_cell) that would return a numeric reference to the fill color in the look up cell.

Try this:

Function CellColor(myRange As Range) As Double
    CellColor = myRange.Interior.Color
End Function

 

[sriche01]

Try this:

Function CellColor(myRange As Range) As Double
    CellColor = myRange.Interior.Color
End Function

Thanks! I will check that out when I get back top work next week!