if color then formula in cell

[Jonsson]

Hi,

Can anyone help me to write a code that says:

IF text in cell B4=red, choose formula in cell C4
IF text in cell B4=black, choose formula in cell D4
IF text in cell B4=grey, choose formula in cell E4

Hope you can understand what I want to do

Any help is apprecciated!

//Thoma

 

[Bob Phillips]

Thomas,

add this UDF

Function CellColour(rng As Range, Optional text As Boolean = True)
If rng.Count > 1 Then
CellColour = CVErr(xlErrValue)
Else
If text Then
CellColour = rng.Font.ColorIndex
Else
CellColour = rng.Interior.ColorIndex
End If
End If
End Function

and call like

=IF(cellcolour(A1)=3,B1,"")

it doesn't update automatically if the cell colour is changed though.


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Jonsson]

Hi Bob,
Thanks for your answer!

I´m new to this, so, what do you mean by "UDF" and "call like"?
I assume there has to be something more than just copy the code?

I´d really need some more explanation to understand what to do.

//Thoma

 

[Frank Kabel]

Hi
for an introduction to macros (UDFs) see:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Bob Phillips]

Jonsson,

A UDF is a user defined function, a specialist function that you can call
from a worksheet. This has to go in a standard code module. To do this,
press Alt-F11 from Excel, then go to menu Insert>Module and paste my code in
there.

Go back to the worksheet, and assuming your coloured text is in A1 and the
formula in B1, type the formula I provided in some other cell. Adjust the
cell references to suit.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)