Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Change color in cell if contains formula?
Hi all,
I want to be able to have cell with formulas show with a different color. I tried with conditional formatting but can't figure out how. Can someone please help me. Thanks in advance, Emilio Limited knowledge of Excel 2003 |
#2
|
|||
|
|||
You need a UDF and test that in the CF.
Function IsFormula(rng As Range) IsFormula = rng.HasFormula End Function -- HTH Bob Phillips "Wind54Surfer" wrote in message ... Hi all, I want to be able to have cell with formulas show with a different color. I tried with conditional formatting but can't figure out how. Can someone please help me. Thanks in advance, Emilio Limited knowledge of Excel 2003 |
#3
|
|||
|
|||
You could use Format|Conditional formatting and a UserDefined Function.
In a General Module: Option Explicit Function IsFormula(rng As Range) IsFormula = rng(1).HasFormula End Function Then back to excel and use this in your Format|conditional formatting rules: Formula is: =isformula(A1) (If A1 is the activecell.) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Wind54Surfer wrote: Hi all, I want to be able to have cell with formulas show with a different color. I tried with conditional formatting but can't figure out how. Can someone please help me. Thanks in advance, Emilio Limited knowledge of Excel 2003 -- Dave Peterson |
#4
|
|||
|
|||
Dave,
DANGER, WILL ROBINSON!!! :) Using a UDF in conditional formatting can cause bizzare display problems. There's a knowledge base article about it. Searching the knowledge base for udf conditional formatting didn't find it, for some reason. I spent hours on a project that was acting as if Excel had gone mad. -- Earl Kiosterud www.smokeylake.com "Dave Peterson" wrote in message ... You could use Format|Conditional formatting and a UserDefined Function. In a General Module: Option Explicit Function IsFormula(rng As Range) IsFormula = rng(1).HasFormula End Function Then back to excel and use this in your Format|conditional formatting rules: Formula is: =isformula(A1) (If A1 is the activecell.) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Wind54Surfer wrote: Hi all, I want to be able to have cell with formulas show with a different color. I tried with conditional formatting but can't figure out how. Can someone please help me. Thanks in advance, Emilio Limited knowledge of Excel 2003 -- Dave Peterson |
#5
|
|||
|
|||
F5 Special Formulas OK...........this will highlight all cells with
formulas therein......... Then, while they are highlighted, right-click FormatCells PatternsTab choose a color OK Vaya con Dios, Chuck, CABGx3 "Wind54Surfer" wrote in message ... Hi all, I want to be able to have cell with formulas show with a different color. I tried with conditional formatting but can't figure out how. Can someone please help me. Thanks in advance, Emilio Limited knowledge of Excel 2003 |
#6
|
|||
|
|||
Thanks for all the help,
Usted tambien Emilio "CLR" wrote: F5 Special Formulas OK...........this will highlight all cells with formulas therein......... Then, while they are highlighted, right-click FormatCells PatternsTab choose a color OK Vaya con Dios, Chuck, CABGx3 "Wind54Surfer" wrote in message ... Hi all, I want to be able to have cell with formulas show with a different color. I tried with conditional formatting but can't figure out how. Can someone please help me. Thanks in advance, Emilio Limited knowledge of Excel 2003 |
#7
|
|||
|
|||
I've never seen that happen (but I do believe you).
Maybe the OP can use the UDF in a helper cell and then base that conditional formatting on that helper cell? (Do you recall if there's a problem with this suggestion?) Earl Kiosterud wrote: Dave, DANGER, WILL ROBINSON!!! :) Using a UDF in conditional formatting can cause bizzare display problems. There's a knowledge base article about it. Searching the knowledge base for udf conditional formatting didn't find it, for some reason. I spent hours on a project that was acting as if Excel had gone mad. -- Earl Kiosterud www.smokeylake.com "Dave Peterson" wrote in message ... You could use Format|Conditional formatting and a UserDefined Function. In a General Module: Option Explicit Function IsFormula(rng As Range) IsFormula = rng(1).HasFormula End Function Then back to excel and use this in your Format|conditional formatting rules: Formula is: =isformula(A1) (If A1 is the activecell.) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Wind54Surfer wrote: Hi all, I want to be able to have cell with formulas show with a different color. I tried with conditional formatting but can't figure out how. Can someone please help me. Thanks in advance, Emilio Limited knowledge of Excel 2003 -- Dave Peterson -- Dave Peterson |
#8
|
|||
|
|||
Dave,
I don't know, but I my guess is referring to a cell from CF containing a UDF is OK. I'm pretty sure I ended up writing a Worksheet_Change routine with the same logic as in the UDF. I haven't reloaded that client's stuff since I replaced my disk drive last week and did a whole new install, so I don't have quick access to that workbook. But I remember how I spent hours, with strange things popping up on the screen (and wouldn't go away). I could hear Twilight Zone music every time I worked on it. It was quite intermittent, and would work OK for days at a time. I keep old versions of workbook files in a project, so I started going back, and it quit failing at the point where I'd put a UDF in CF. When I took that out of the most current file, it worked fine. I posted in the MVP private group about it after I found the solution. -- Earl Kiosterud www.smokeylake.com "Dave Peterson" wrote in message ... I've never seen that happen (but I do believe you). Maybe the OP can use the UDF in a helper cell and then base that conditional formatting on that helper cell? (Do you recall if there's a problem with this suggestion?) Earl Kiosterud wrote: Dave, DANGER, WILL ROBINSON!!! :) Using a UDF in conditional formatting can cause bizzare display problems. There's a knowledge base article about it. Searching the knowledge base for udf conditional formatting didn't find it, for some reason. I spent hours on a project that was acting as if Excel had gone mad. -- Earl Kiosterud www.smokeylake.com "Dave Peterson" wrote in message ... You could use Format|Conditional formatting and a UserDefined Function. In a General Module: Option Explicit Function IsFormula(rng As Range) IsFormula = rng(1).HasFormula End Function Then back to excel and use this in your Format|conditional formatting rules: Formula is: =isformula(A1) (If A1 is the activecell.) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Wind54Surfer wrote: Hi all, I want to be able to have cell with formulas show with a different color. I tried with conditional formatting but can't figure out how. Can someone please help me. Thanks in advance, Emilio Limited knowledge of Excel 2003 -- Dave Peterson -- Dave Peterson |
#9
|
|||
|
|||
Thanks for the background.
Earl Kiosterud wrote: Dave, I don't know, but I my guess is referring to a cell from CF containing a UDF is OK. I'm pretty sure I ended up writing a Worksheet_Change routine with the same logic as in the UDF. I haven't reloaded that client's stuff since I replaced my disk drive last week and did a whole new install, so I don't have quick access to that workbook. But I remember how I spent hours, with strange things popping up on the screen (and wouldn't go away). I could hear Twilight Zone music every time I worked on it. It was quite intermittent, and would work OK for days at a time. I keep old versions of workbook files in a project, so I started going back, and it quit failing at the point where I'd put a UDF in CF. When I took that out of the most current file, it worked fine. I posted in the MVP private group about it after I found the solution. -- Earl Kiosterud www.smokeylake.com "Dave Peterson" wrote in message ... I've never seen that happen (but I do believe you). Maybe the OP can use the UDF in a helper cell and then base that conditional formatting on that helper cell? (Do you recall if there's a problem with this suggestion?) Earl Kiosterud wrote: Dave, DANGER, WILL ROBINSON!!! :) Using a UDF in conditional formatting can cause bizzare display problems. There's a knowledge base article about it. Searching the knowledge base for udf conditional formatting didn't find it, for some reason. I spent hours on a project that was acting as if Excel had gone mad. -- Earl Kiosterud www.smokeylake.com "Dave Peterson" wrote in message ... You could use Format|Conditional formatting and a UserDefined Function. In a General Module: Option Explicit Function IsFormula(rng As Range) IsFormula = rng(1).HasFormula End Function Then back to excel and use this in your Format|conditional formatting rules: Formula is: =isformula(A1) (If A1 is the activecell.) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Wind54Surfer wrote: Hi all, I want to be able to have cell with formulas show with a different color. I tried with conditional formatting but can't figure out how. Can someone please help me. Thanks in advance, Emilio Limited knowledge of Excel 2003 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional cell shading when a change occurs | Excel Discussion (Misc queries) | |||
SQL - Auto Refresh Upon Cell Value Change | Excel Worksheet Functions | |||
How do I create formula to change cell color | Excel Worksheet Functions | |||
How can I write an if-then formula for 0 or less than 0 in cell t. | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |