Formatting a Cell within a function

**Neil07979** · 11-25-2008, 06:09 AM

All,

I'm having some difficulties with formatting of a cell.

I am trying to color the cell, and change the font based on some simple if statements within the function.

Here's some of the code (Simplified) that I am trying to get to work:

Function TAM_RAG(ClientExperience, JPPerformance, Green, Amber, RMin, RMaj, Indicator)

Dim cl     As Range

TAM_RAG = ClientExperience
    
    With Selection.Interior
        .ColorIndex = 10
        .Pattern = xlSolid
    End With

End Function

I think I may have the syntax wrong but cant have tried many other ways.

Any help would be greatly appreciated.

Thanks

**royUK** · 11-25-2008, 06:25 AM

A Function should not be used to change a cell.

**Neil07979** · 11-25-2008, 08:14 AM

I see - so I'm assuming the best way to do this would be to create a macro that would run after an event has taken place?

**royUK** · 11-25-2008, 08:17 AM

Have you considered Conditional Formatting?

**Shijesh Kumar** · 11-25-2008, 08:47 AM

Hi

You cant change the format of a cell using Function.

Even if a function is calling a macros to format the cell... it does not work...

**Neil07979** · 11-25-2008, 08:51 AM

I have considered and tried conditional formating - however the formats I want to create are to complex, to my knowledge, to be done within that function.

Basically - I have 2 sets of data, one is an overall performance and the other is an internal performance - both based on the same targets. The overall performance is the figure that is shown in the results, and changes font color based on the RAG targets (We have an addtional Red Major target too - so there are 4 possible colors.) The Internal performance is then used to RAG rate the Internal performance and is used to color the backround of the same results cell. There is a possible 10 different combinations of cell and font colours that I need to build in.

I dont think this can be done on the Conditional formating?
Any ideas?