In , Don Guillett
spake thusly:

I really can't tell what you are talking about because there is
no clue in the post. Might have been

Oh, my. Sorry. I assumed others could grab messages from the
References headers as easily as I can. I've only ever used
a Unix Newsreader. Now I'm beginning to understand the penchant
for top-posting around here. :-)

for i =1 to 21
for ii=3 to 81
could have just as easily been
for dman=3 to 81
or whatever you want to call it.

No, but I'll post the entire thing again below.
Basically, my relative-reference named ranges
weren't working right. But not only that,
they were saying "II:II" instead of anything
comprehensible to me. Dave gave me the answer
I quoted, but I couldn't find any trace of
information about such via Google and asked him to
explain. But he didn't come back to the thread.

Here, then, is the post that clarifies it best.
The original thing I asked is way down near the
bottom.

Muchas gracias, and sorry to have perplexed anyone:

=dman=

===============
Path: reader2.panix.com!panix!panix.com!dman
From: Dallman Ross <dman@localhost.
Newsgroups: microsoft.public.excel.misc
Subject: Inexplicable VBA errors in formulas
Date: Sun, 22 Jul 2007 11:38:52 +0000 (UTC)
Organization: Crossover Fuzzy
Lines: 56
Message-ID:
References: .com
NNTP-Posting-Host: panix5.panix.com
X-Trace: reader2.panix.com 1185104332 13611 166.84.1.5 (22 Jul 2007 11:38:52 GMT)
X-Complaints-To:
NNTP-Posting-Date: Sun, 22 Jul 2007 11:38:52 +0000 (UTC)
User-Agent: tin/1.6.2-20030910 ("Pabbay") (UNIX) (NetBSD/3.1_RC3 (i386))
Xref: panix microsoft.public.excel.misc:564042

In .com, Dave O
spake thusly:

Those aren't roman numerals, they're column ii (eye-eye)
references.

Dave, can you clarify further about that? I can't find anything
useful via the usual search engines on the web for "eye-eye."
In any case, they cause my formatting formula not to work.

Anyway, though, here is a happy update: I solved my problem. I
changed the code simply by making the column references absolute
rather than relative, and the "II:II" stuff went away and "$B:$B"
showed up and my conditonal formats are now working. I can't
explain it, though, and I'd sure like to understand it.

Again, here was the code. "myRow" is the last row in the column:

' data validation for Order #
With .Range("B2", Cells(myRow, 2))
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF(B:B,B2)1"
.FormatConditions(1).Interior.ColorIndex = 22 ' watermelon-pink

.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(ROW(),2)"
.FormatConditions(2).Interior.ColorIndex = 24
End With


All I changed is,

"=COUNTIF($B:$B,$B2)1"

and now it works.

I had written:

I run the macro. Things that should be pink aren't. I look in
the conditonal formatting for the cells in question. I see,
e.g., for cell B2:

=COUNTIF(II:II,HI3)1

Can someone explain that?!

[snip]


Oh, and that "3" instead of "2" was no typo, either. But now it
says 2, since I switched to absolute column references. I can't
explain it!

--
dman