asked on

Change a cell's color based on another cell's color

I created a macro, "GetFontColor":

Function GetFontColor(Rng As Range) As Long
GetFontColor = Rng.Font.Color
End Function

Now I need to format the font color for a range of cells using the results of the function. So for example, if I format a cell's text to red, the formula returns "255". So in another cell, I would like to also change the color to the referenced cell's color (or any other color for that matter). I think I can do this with conditional formatting using a formula but it doesn't seem to work: Format using a formula... type "GetFontColor($A$1)=255" then format the font color red. Nothing happens.

ASKER CERTIFIED SOLUTION

als315

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

orerockon

ASKER

OK great, that works fine. I should have explained myself better, the function GetFontColor is stored in the personal workbook (which is open) and I'm trying to apply it to another open workbook, where it didn't work. I didn't think to try the function in that workbook, and it returned #NAME?. I have other macros stored in the personal workbook that work in all open books. I copied the module to the workbook I am using, saved it as .xlsm, and it worked. I saved the file as .xlsx, opened it again, didn't work. Restarted Excel, it works. I'm guessing I have to close and open Excel each time I make changes in personal.xlsb. Thanks again.