Trying to use an IF formula w/ Colored Cell 2

[waladd]

I'm trying to create a formula that reads the cell background color and reports a specific answer for that cell. My Human Resources department needs the employee schedule one way, while my employees are use to looking at it in a totally different format. HR uses a color key to code whether someone is sick, on in one area or another etc. The colors are listed in excel as Red, Rose, Light Green, Light Yellow. There is no conditional formating, I have not used VB editor before. These schedules are in two different workbooks. I'm using Office 2003 Pro.

 

[SkipVought]

Hi,

Copy this function and paste in a MODULE in your VB Editor - Tools/Macro/Visual Basic Editor - Insert/Module

Skip,

_[red][/red]

 

[SkipVought]

...Sorry

Function WhatInteriorColor(rng As Range) As String
    'Red, Rose, Light Green, Light Yellow
    Select Case rng.Interior.ColorIndex
        Case 3
            WhatInteriorColor = "Red"
        Case 38
            WhatInteriorColor = "Rose"
        Case 35
            WhatInteriorColor = "Light Green"
        Case 36
            WhatInteriorColor = "Light Yellow"
        Case Else
            WhatInteriorColor = "Other"
    End Select
End Function

You can use this function on your sheetm as you would any other function. Insert/Formula - User Defined...

Skip,

_[red][/red]

 

[SkipVought]

By the way, the values that are tested in this function, ASSUME that the Standard Collor Pallet is being used.

The color pallet can be changed via Tools/Options - Color tab and changing the pallet, can change the results of this function.

Skip,

_[red][/red]

 

[waladd]

ok, only need to know if I can do things such as IF "Rose" then "L", if "Light Yellow" then "ON". I appreciate this so much, thank you.

 

[waladd]

Tried this:
=WhatInteriorColor('[ANCILLARY 2007.xls]0211-0224'!$F$7)*IF('[ANCILLARY 2007.xls]0211-0224'!$F$7="Rose","L",IF('[ANCILLARY 2007.xls]0211-0224'!$F$7="Light Yellow","ON",IF('[ANCILLARY 2007.xls]0211-0224'!$F$7="Red","SICK",IF('[ANCILLARY 2007.xls]0211-0224'!$F$7="Light Green","PH","OFF"))))

But getting #Value error

 

[SkipVought]

The WhatInteriorColor takes ONE argument, that is the cell reference that you want to know the color of.
[tt]
=if(WhatInteriorColor('[ANCILLARY 2007.xls]0211-0224'!$F$7)="Rose","L",if(WhatInteriorColor('[ANCILLARY 2007.xls]0211-0224'!$F$7)="Light Yellow","ON",if(WhatInteriorColor('[ANCILLARY 2007.xls]0211-0224'!$F$7)="Red","SICK,if(WhatInteriorColor('[ANCILLARY 2007.xls]0211-0224'!$F$7)="Light Green","PH","OFF"))))
[/tt]


Skip,

_[red][/red]

 

[waladd]

Perfect Thank You SOOOO Much.


:-D