recognizing cell.interior.colorindex

  • Thread starter Thread starter BillRobPV
  • Start date Start date
B

BillRobPV

I adapted Pearson's code to write a macro to test if a cell had a
colorindex>0, then (if so) remove all conditional formatting that colored the
cell (leaving the color). It works fine on a small test spreadsheet with
different test conditional formats.
When I try to run my "real" application, it always returns -4142 (clear) for
cell.interior.colorindex, regardless of the cell color. It doesn't seem
related to Pearson's code. Any help appreciated. TIA
 
Chip has lots of code on his site. I don't know what you used to check.

But testing the .colorindex won't work if the color is provided by conditional
formatting.
 
Thank you Dave. I used Pearson's "Active Condition" and "ColorIndexofCF". My
test cells in my "development" spreadsheet are (as best as I know), colored
only with conditional formats, but it appears they must be also colored in
some way that colorindex returns a value. I will keep trying with Pearson's
colorindexofCF function.
 
Dave, I reread Pearson's note: "ActiveCondition my result in an inaccurate
result if the following are true: 1) You are calling ActiveCondition from a
worksheet cell (I am), AND 2) the cell passed to ActiveCondition uses a
"Formula Is" rather than "Cell Value Is" condition (it does not), AND 3)the
formula used in the condition formula contains relative addresses". The
formula is "Cell Value Is greater than $T$10*J2". I don't know if this
condition is a problem relative to his caveats.
I loaded Pearson's code directly and completely. When I step through, I
basically run functions ColorIndexofCF, ActiveCondition, and
GetStrippedValue. it seems to run OK, but it does not get the colorindex.
It sets the colorindex to -4142, regardless of the formatting. I set the cell
as a range. In that cell the code is just m=colorindexofCF(cell,False). Is
my problem related to the way I call the function?? Thanks for your help.


--
Bill Roberts


BillRobPV said:
Thank you Dave. I used Pearson's "Active Condition" and "ColorIndexofCF". My
test cells in my "development" spreadsheet are (as best as I know), colored
only with conditional formats, but it appears they must be also colored in
some way that colorindex returns a value. I will keep trying with Pearson's
colorindexofCF function.
Click to expand...
 
Maybe...

I did some experimenting with Chip's functions and your formula and I got the
same results as you.

But if I changed the CF (in my test cell, A1) to:
Formula is:
=$A$1>$T$10*$J$2

it returned a nice number.

Bob Phillips has some notes for conditional formatting, but his site is
currently down:
http://www.xldynamic.com/
 
Thanks. At my level of expertise, I keep trying different dimension
statements, range conditions, etc. because I don't know any better. It seems
to me the bottom line is it is not possible to iterate through a range (that
uses any relative conditional format rules), and delete the conditional
format rules while maintaining the color codes in the cells. Do you agree???
I want to sort the spreadsheet on the various columns, but if the relative
conditional format stays with the cells, it is all screwed up. Am I missing
something or is there just no way to code it???
 
Working with conditional formatting is a pain. I know that I find it easier to
use a helper column and duplicate the conditions in a formula. Then I can base
whatever I need on that helper column's value.

I wouldn't say that there's no way to code it. Just that I haven't (and won't)
bother looking too closely.
Thanks. At my level of expertise, I keep trying different dimension
statements, range conditions, etc. because I don't know any better. It seems
to me the bottom line is it is not possible to iterate through a range (that
uses any relative conditional format rules), and delete the conditional
format rules while maintaining the color codes in the cells. Do you agree???
I want to sort the spreadsheet on the various columns, but if the relative
conditional format stays with the cells, it is all screwed up. Am I missing
something or is there just no way to code it???
Click to expand...
 
Thanks Dave. I appreciate the help. No more posts, but if you have any
further thoughts, please let me know at (e-mail address removed).
 
Bookmark Bob Phillips' site. When it's back on line, you may find what you
need.
Thanks Dave. I appreciate the help. No more posts, but if you have any
further thoughts, please let me know at (e-mail address removed).
--
Bill Roberts

Dave Peterson said:
Working with conditional formatting is a pain. I know that I find it easier to
use a helper column and duplicate the conditions in a formula. Then I can base
whatever I need on that helper column's value.

I wouldn't say that there's no way to code it. Just that I haven't (and won't)
bother looking too closely.
Click to expand...
Click to expand...
 
Back
Top