I would like to colour code all the cells in my spreadsheet based on whether
they contain a formula or not.

is this possible?

http://www.j-walk.com/ss/excel/usertips/tip045.htm

Regards,
Bill


"ellebelle" wrote:

I would like to colour code all the cells in my spreadsheet based on whether
they contain a formula or not.

is this possible?

that is great, although what if i want the opposite effect. everytime i enter
a value manuallly i want to highlight it?

"Bill Pfister" wrote:


http://www.j-walk.com/ss/excel/usertips/tip045.htm

Regards,
Bill


"ellebelle" wrote:

I would like to colour code all the cells in my spreadsheet based on whether
they contain a formula or not.

is this possible?

Set the cells so that they are normally highlighted and use the conditional
formatting to make the cell appear normal when the condition is true.

Instead of using the formula ("CellHasFormula") as a named-range, you can
use a user-defined function in the condition.

Put this code in any module of the workbook:

public function IsConstant()
IsConstant = not(application.caller.hasformula)
end function

Then use "=IsConstant()" as your formula in the conditional formatting.




"ellebelle" wrote:

that is great, although what if i want the opposite effect. everytime i enter
a value manuallly i want to highlight it?

"Bill Pfister" wrote:


http://www.j-walk.com/ss/excel/usertips/tip045.htm

Regards,
Bill


"ellebelle" wrote:

I would like to colour code all the cells in my spreadsheet based on whether
they contain a formula or not.

is this possible?

that works, although can you get it to ignore empty cells?

"Bill Pfister" wrote:

Set the cells so that they are normally highlighted and use the conditional
formatting to make the cell appear normal when the condition is true.

Instead of using the formula ("CellHasFormula") as a named-range, you can
use a user-defined function in the condition.

Put this code in any module of the workbook:

public function IsConstant()
IsConstant = not(application.caller.hasformula)
end function

Then use "=IsConstant()" as your formula in the conditional formatting.




"ellebelle" wrote:

that is great, although what if i want the opposite effect. everytime i enter
a value manuallly i want to highlight it?

"Bill Pfister" wrote:


http://www.j-walk.com/ss/excel/usertips/tip045.htm

Regards,
Bill


"ellebelle" wrote:

I would like to colour code all the cells in my spreadsheet based on whether
they contain a formula or not.

is this possible?

Public Function IsConstant()
If (Len(Application.Caller.Value) = 0) Then
IsConstant = False
Else
IsConstant = Not (Application.Caller.HasFormula)
End If
End Function



"ellebelle" wrote:

that works, although can you get it to ignore empty cells?

"Bill Pfister" wrote:

Set the cells so that they are normally highlighted and use the conditional
formatting to make the cell appear normal when the condition is true.

Instead of using the formula ("CellHasFormula") as a named-range, you can
use a user-defined function in the condition.

Put this code in any module of the workbook:

public function IsConstant()
IsConstant = not(application.caller.hasformula)
end function

Then use "=IsConstant()" as your formula in the conditional formatting.




"ellebelle" wrote:

that is great, although what if i want the opposite effect. everytime i enter
a value manuallly i want to highlight it?

"Bill Pfister" wrote:


http://www.j-walk.com/ss/excel/usertips/tip045.htm

Regards,
Bill


"ellebelle" wrote:

I would like to colour code all the cells in my spreadsheet based on whether
they contain a formula or not.

is this possible?