| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting based on text within a formula
Bienvenue !
:-] -- Regards, Luc. "Festina Lente" "Sarah Jane" wrote: THANK YOU! THANK YOU! THANK YOU! I finally got back to that project to try your suggestion. It worked perfectly!!! Now I don't have to worry if I forgot to add or remove the "/2" in certain cells because the cells with the "/2" are highlighted which makes it easy to see if I got all the appropriate cells. Again, thank you so much. Sarah Jane "PapaDos" wrote: "User defined function", a custom macro that you can use as a worksheet function. In your case a simple one would do, like: Function getCellFormula(r As Range) getCellFormula = r.Formula End Function If you enter =getcellFormula(a1) in a cell, the cell will contain the formula of the A1 cell... So for your conditional formatting, try this formula: =RIGHT(SUBSTITUTE(getCellFormula(B11), " ", ""), 2) = "/2" -- Festina Lente "Sarah Jane" wrote: I am not familiar with UDF. Can you give me some additional information about it? Thanks, Sarah Jane "PapaDos" wrote: Why not creating a UDF that returns a cell formula and use it in your conditional formatting conditions ? -- Festina Lente "Sarah Jane" wrote: I have several cells with formulas in them, and other cells have the same formula divided by 2. My real formulas are rather complex, i.e. Cell X6 is =INDEX(act!$A:$IR,MATCH($D6,act!$D:$D,0),MATCH(X$1 ,act!$1:$1,0))/2. So, here is a very simplified example: A B C row 1: =Sheet1!A1 =Sheet1!B1 =Sheet1!C1 row 2: =Sheet1!A2/2 =Sheet1!B2/2 =Sheet1!C2/2 row 3: =Sheet1!A3 =Sheet1!B3 =Sheet1!C3 row 4: =Sheet1!A4 =Sheet1!B4 =Sheet1!C4 row 5: =Sheet1!A5/2 =Sheet1!B5/2 =Sheet1!C5/2 I want to use the conditional formatting option to highlight the cells that are divided by 2. I tried "Formula Is =IF(RIGHT(A2,2)="/2",TRUE,FALSE)=TRUE"; but since Excel 2003 SP2 is using the value of A2 in the calculation instead of looking at the formula, I never get a true condition. Then I thought about trying to replace the A2 in the conditional formula mentioned above with CONCATENATE("'",A2) to fool Excel into thinking the cell was formatted as text, but that didn't work either because it still returned the value--only with an ' in front of it. I have thousands of cells using these formulas, so creating helper columns with text versions of the formulas is not possible, or at least not practical. I think I could accomplish this with a macro, but I don't want to have to run a macro every time I edit a cell or add more rows or columns. Therefore, if you know of an Excel formula that I can use to access a formulas text or you have any other suggestions, PLEASE, PLEASE let me know. Thanks, Sarah Jane Bowers |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional Formatting Against a Formula - not it's result | Excel Discussion (Misc queries) | |||
| Changing text color usinf a formula (NOT Conditional Formatting) | Excel Discussion (Misc queries) | |||
| Conditional Formatting - Formula based | Excel Worksheet Functions | |||
| conditional formatting based on another cells formula result | Excel Discussion (Misc queries) | |||
| Conditional formatting based on text | Excel Worksheet Functions | |||
Hybrid Mode
