bob phillips, question bout conditional format white paper

[mwam423]

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.

 

[Bob Phillips]

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]

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]

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]

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]

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]

hi bob, i'll take a look, thanks and have a great weekend

 

[mwam423]

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: =a1>A$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

 

[Madiya]

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: =a1>A$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

 

[Norman Jones]

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

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:
=a1>A$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

 

[mwam423]

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!

 

[Bob Phillips]

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]

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 . .