G
Guest
I would like to colour code all the cells in my spreadsheet based on whether
they contain a formula or not.
is this possible?
they contain a formula or not.
is this possible?
Install the app
How to install the app on iOS
Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
G
Guest
G
Guest
G
Guest
that is great, although what if i want the opposite effect. everytime i enter
a value manuallly i want to highlight it?
a value manuallly i want to highlight it?
G
Guest
that is great, although what if i want the opposite effect. everytime i enter
a value manuallly i want to highlight it?
a value manuallly i want to highlight it?
G
Guest
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.
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.
G
Guest
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.
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.
G
Guest
that works, although can you get it to ignore empty cells?
G
Guest
that works, although can you get it to ignore empty cells?
G
Guest
Public Function IsConstant()
If (Len(Application.Caller.Value) = 0) Then
IsConstant = False
Else
IsConstant = Not (Application.Caller.HasFormula)
End If
End Function
If (Len(Application.Caller.Value) = 0) Then
IsConstant = False
Else
IsConstant = Not (Application.Caller.HasFormula)
End If
End Function
G
Guest
Public Function IsConstant()
If (Len(Application.Caller.Value) = 0) Then
IsConstant = False
Else
IsConstant = Not (Application.Caller.HasFormula)
End If
End Function
If (Len(Application.Caller.Value) = 0) Then
IsConstant = False
Else
IsConstant = Not (Application.Caller.HasFormula)
End If
End Function
G
Guest
thank you. that works perfectly.
Bill Pfister said:Public Function IsConstant()
If (Len(Application.Caller.Value) = 0) Then
IsConstant = False
Else
IsConstant = Not (Application.Caller.HasFormula)
End If
End Function
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.