Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
bob phillips, question bout conditional format white paper
hi bob, am using your CFcolorcount formula over multiple ranges and while it
gets me good values when typed in, i can't seem to get the formula to update when there is change in data, which results in change in conditional format. has anyone else run into this problem? any help greatly appreciated. fyi, this function is a godsend(!) as you got around the requirement of having fixed values in the conditional formula. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
bob phillips, question bout conditional format white paper
I have just tried this now with a range of 20 numbers, and two conditions.
Using the two functions, CFColorindex, and CFArrayColours, I got the expected results. I then changed a value so that it became formatted, and the formula cell updated correctly. So, it is working here. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "mwam423" wrote in message ... hi bob, am using your CFcolorcount formula over multiple ranges and while it gets me good values when typed in, i can't seem to get the formula to update when there is change in data, which results in change in conditional format. has anyone else run into this problem? any help greatly appreciated. fyi, this function is a godsend(!) as you got around the requirement of having fixed values in the conditional formula. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
bob phillips, question bout conditional format white paper
wanted to point out possible error in code shown in figure 8 of the white
paper: CFColorCount = CFColorCount - _ CLng(CFColorindex(cell, text) )= ciValue) kept getting error so changed second line, removing the second parenthesis after "text": CLng(CFColorindex(cell, text) = ciValue) this seemingly works, or could this be causing recalc problem? otherwise the code is exactly what's in your white paper . . "Bob Phillips" wrote: I have just tried this now with a range of 20 numbers, and two conditions. Using the two functions, CFColorindex, and CFArrayColours, I got the expected results. I then changed a value so that it became formatted, and the formula cell updated correctly. So, it is working here. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "mwam423" wrote in message ... hi bob, am using your CFcolorcount formula over multiple ranges and while it gets me good values when typed in, i can't seem to get the formula to update when there is change in data, which results in change in conditional format. has anyone else run into this problem? any help greatly appreciated. fyi, this function is a godsend(!) as you got around the requirement of having fixed values in the conditional formula. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
bob phillips, question bout conditional format white paper
Thanks for the spot. Someone did tell me about that once before, but I
forgot the details, so didn't correct it ... will do now. Okay, so back to the issue. I installed this function, made that correction, and changed values in my range, and the formula cell updated correctly. So it is not your correction that is causing the problem, it must be something else in your setup. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "mwam423" wrote in message ... wanted to point out possible error in code shown in figure 8 of the white paper: CFColorCount = CFColorCount - _ CLng(CFColorindex(cell, text) )= ciValue) kept getting error so changed second line, removing the second parenthesis after "text": CLng(CFColorindex(cell, text) = ciValue) this seemingly works, or could this be causing recalc problem? otherwise the code is exactly what's in your white paper . . "Bob Phillips" wrote: I have just tried this now with a range of 20 numbers, and two conditions. Using the two functions, CFColorindex, and CFArrayColours, I got the expected results. I then changed a value so that it became formatted, and the formula cell updated correctly. So, it is working here. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "mwam423" wrote in message ... hi bob, am using your CFcolorcount formula over multiple ranges and while it gets me good values when typed in, i can't seem to get the formula to update when there is change in data, which results in change in conditional format. has anyone else run into this problem? any help greatly appreciated. fyi, this function is a godsend(!) as you got around the requirement of having fixed values in the conditional formula. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
bob phillips, question bout conditional format white paper
could you be alittle more specific regarding setup, i'm not clear what that
means. i've tried deleting code and copying code directly from white paper. this has actually caused different problem. i get a compile error: wrong number of arguments which highlights CFcolorindex function in CFcolorcount code. if i remove the ", text" after rng and cell, in closed parentheses, it seems to work (does this make sense? ) but still have the same problem described above. namely, the CFcolorcount function does not update when i repeatedly hit F9, or when running macro using "calculate". only recalcs when i goto cell, hit F2, then hit Enter. weird thing is, when i did pretty much the exact same thing, the first time, i.e. copy code from white paper to module, didn't get the compile error . . as you can see i'm lost here. any ideas? "Bob Phillips" wrote: Thanks for the spot. Someone did tell me about that once before, but I forgot the details, so didn't correct it ... will do now. Okay, so back to the issue. I installed this function, made that correction, and changed values in my range, and the formula cell updated correctly. So it is not your correction that is causing the problem, it must be something else in your setup. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
bob phillips, question bout conditional format white paper
I have posted an example at
http://cjoint.com/?fEu323LkCx -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "mwam423" wrote in message ... could you be alittle more specific regarding setup, i'm not clear what that means. i've tried deleting code and copying code directly from white paper. this has actually caused different problem. i get a compile error: wrong number of arguments which highlights CFcolorindex function in CFcolorcount code. if i remove the ", text" after rng and cell, in closed parentheses, it seems to work (does this make sense? ) but still have the same problem described above. namely, the CFcolorcount function does not update when i repeatedly hit F9, or when running macro using "calculate". only recalcs when i goto cell, hit F2, then hit Enter. weird thing is, when i did pretty much the exact same thing, the first time, i.e. copy code from white paper to module, didn't get the compile error . . as you can see i'm lost here. any ideas? "Bob Phillips" wrote: Thanks for the spot. Someone did tell me about that once before, but I forgot the details, so didn't correct it ... will do now. Okay, so back to the issue. I installed this function, made that correction, and changed values in my range, and the formula cell updated correctly. So it is not your correction that is causing the problem, it must be something else in your setup. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
bob phillips, question bout conditional format white paper
hi bob, i'll take a look, thanks and have a great weekend |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
bob phillips, question bout conditional format white paper
hi bob, i've recreated what's happening in my model. in cell A22 put in formula: =A23*A24; A23, =rand(); A24, 50 (or a number) change conditional format in A1 as follows: condition 1, formula: =a1A$22; delete condition 2, then copy "format-only" down to A20. change value in A24. as value in A22 changes, color format in A1 through A20 will change, however value in E5 doesn't reflect change. let me know if you have any questions, comments, thanks |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
bob phillips, question bout conditional format white paper
On May 31, 4:24*am, mwam423 wrote:
hi bob, i've recreated what's happening in my model. *in cell A22 put in formula: =A23*A24; A23, =rand(); A24, 50 (or a number) change conditional format in A1 as follows: condition 1, formula: =a1A$22; delete condition 2, then copy "format-only" down to A20. change value in A24. *as value in A22 changes, color format in A1 through A20 will change, however value in E5 doesn't reflect change. *let me know if you have any questions, comments, thanks Sorry to intrupt in between, but can you pl let me know where these coler functions are available and possibly any write-up for the same? Regards, Madiya |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
bob phillips, question bout conditional format white paper
Hi Madiya,
Visit Bob's xlDynamic Site: http://www.xldynamic.com/source/xld.html More particularly, see Bob's Testing CF Conditions page at: http://www.xldynamic.com/source/xld.CFConditions.html --- Regards. Norman "Madiya" wrote in message ... On May 31, 4:24 am, mwam423 wrote: hi bob, i've recreated what's happening in my model. in cell A22 put in formula: =A23*A24; A23, =rand(); A24, 50 (or a number) change conditional format in A1 as follows: condition 1, formula: =a1A$22; delete condition 2, then copy "format-only" down to A20. change value in A24. as value in A22 changes, color format in A1 through A20 will change, however value in E5 doesn't reflect change. let me know if you have any questions, comments, thanks Sorry to intrupt in between, but can you pl let me know where these coler functions are available and possibly any write-up for the same? Regards, Madiya |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
bob phillips, question bout conditional format white paper
hi bob, i owe you an apology. reread the white paper and per "further enhancements" section, added "application.volatile" to CFcolorindex formula. now i'm able to recalc CFcolorcount, on a worksheet by worksheet basis, with F9 or "calculate" in code, which pretty much replicates what i had to do with c. pearson's countofCF formula. my apologies and thanks for the great work! |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
bob phillips, question bout conditional format white paper
Yeah the difference is though that Chip's function only handles conditions
using the Condition Is option, mine handles that and Formula. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "mwam423" wrote in message ... hi bob, i owe you an apology. reread the white paper and per "further enhancements" section, added "application.volatile" to CFcolorindex formula. now i'm able to recalc CFcolorcount, on a worksheet by worksheet basis, with F9 or "calculate" in code, which pretty much replicates what i had to do with c. pearson's countofCF formula. my apologies and thanks for the great work! |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
bob phillips, question bout conditional format white paper
hi bob, chip's formula works with formulas as i was using it before i switched to CFcolorcount. the only problem was that each variable in formula had to be fixed variable making it impossible to copy the format to other cells. i still have this problem though: i have seven sheets and each has a bottom row, with your formula across it, previously it was chip's formula, which counts how many format changes occurred in the column above. the only way i get good numbers is to select/activate each individual sheet, and then "calculate" the activated/selected sheet. otherwise the values are incorrect. additionally, when i move to another sheet and calculate, that bottom row on previous sheets i've run "calculate" on has changed and reflects incorrect values. go figure . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to print white letters on colored paper? | Excel Discussion (Misc queries) | |||
Dynamic Dependent Dropdown - one question for Mr. Phillips | Excel Discussion (Misc queries) | |||
Question to Bob Phillips (or whoever...) | Excel Worksheet Functions | |||
Question for Bob Phillips re Splitting Names from Cells | Excel Discussion (Misc queries) | |||
Bob Phillips, I have one more question | Excel Worksheet Functions |