Hi,

Is it possible to do a formula like when cell background (or font color) is
red, a certain cell will return some value?

Thanks!

Possible with a VBA-Function like :

Function cValue(rng As Range, xValue)
Application.Volatile
If rng.Interior.ColorIndex = 3 Or rng.Font.ColorIndex = 3 Then cValue = xValue
End Function

in ur sheet type : =cValue(any-cell,any-value)


"Tim" skrev:

Hi,

Is it possible to do a formula like when cell background (or font color) is
red, a certain cell will return some value?

Thanks!

Just a note. Changing a cells background colour does not force a calculation.
So when you first change the background the value will not show up. It will
show up as soon as any action is taken that cuases a calculation anywhere in
the application.
--
HTH...

Jim Thomlinson


"excelent" wrote:

Possible with a VBA-Function like :

Function cValue(rng As Range, xValue)
Application.Volatile
If rng.Interior.ColorIndex = 3 Or rng.Font.ColorIndex = 3 Then cValue = xValue
End Function

in ur sheet type : =cValue(any-cell,any-value)


"Tim" skrev:

Hi,

Is it possible to do a formula like when cell background (or font color) is
red, a certain cell will return some value?

Thanks!

Can you teach me how to do that? Please in detail... Many thanks in advance! :)

"excelent" wrote:

Possible with a VBA-Function like :

Function cValue(rng As Range, xValue)
Application.Volatile
If rng.Interior.ColorIndex = 3 Or rng.Font.ColorIndex = 3 Then cValue = xValue
End Function

in ur sheet type : =cValue(any-cell,any-value)


"Tim" skrev:

Hi,

Is it possible to do a formula like when cell background (or font color) is
red, a certain cell will return some value?

Thanks!

Hi
A good and a bad news!

I did it! :-)

But, the file became 4MB in size... :'(

"Tim" wrote:

Can you teach me how to do that? Please in detail... Many thanks in advance! :)

"excelent" wrote:

Possible with a VBA-Function like :

Function cValue(rng As Range, xValue)
Application.Volatile
If rng.Interior.ColorIndex = 3 Or rng.Font.ColorIndex = 3 Then cValue = xValue
End Function

in ur sheet type : =cValue(any-cell,any-value)


"Tim" skrev:

Hi,

Is it possible to do a formula like when cell background (or font color) is
red, a certain cell will return some value?

Thanks!

Brand new workbook with 3 tabs and a x in A1 : 13,5 KB
Same workbook with my Function : 19,0 KB

dono what goes wron in ur case :-)


"Tim" skrev:

Hi
A good and a bad news!

I did it! :-)

But, the file became 4MB in size... :'(

"Tim" wrote:

Can you teach me how to do that? Please in detail... Many thanks in advance! :)

"excelent" wrote:

Possible with a VBA-Function like :

Function cValue(rng As Range, xValue)
Application.Volatile
If rng.Interior.ColorIndex = 3 Or rng.Font.ColorIndex = 3 Then cValue = xValue
End Function

in ur sheet type : =cValue(any-cell,any-value)


"Tim" skrev:

Hi,

Is it possible to do a formula like when cell background (or font color) is
red, a certain cell will return some value?

Thanks!

Maybe because I copied it to an entire column...

"excelent" wrote:

Brand new workbook with 3 tabs and a x in A1 : 13,5 KB
Same workbook with my Function : 19,0 KB

dono what goes wron in ur case :-)


"Tim" skrev:

Hi
A good and a bad news!

I did it! :-)

But, the file became 4MB in size... :'(

"Tim" wrote:

Can you teach me how to do that? Please in detail... Many thanks in advance! :)

"excelent" wrote:

Possible with a VBA-Function like :

Function cValue(rng As Range, xValue)
Application.Volatile
If rng.Interior.ColorIndex = 3 Or rng.Font.ColorIndex = 3 Then cValue = xValue
End Function

in ur sheet type : =cValue(any-cell,any-value)


"Tim" skrev:

Hi,

Is it possible to do a formula like when cell background (or font color) is
red, a certain cell will return some value?

Thanks!