I've been searching the archives but don't even know where to begin
because I don't know what function would be used in Excel to do this.
After some time, it just occurred to me that even conditional
formatting would work. An odd number of characters (I have code to
remove punctuation and spaces that I'd run on the text string first),
would make the cell turn red and even number of characters would turn
the cell background green because I need even-numbered text strings
and this would test for that before I go further.

I know something can be done because of this one message I managed to
find:
http://groups.google.ca/group/micros...a1f0de1cf496d2
but that's all there seems to be as Excel users are usu. concerned
with odd- and even-numbered rows rather than the length of a string of
text. tia! :oD

On Fri, 06 Apr 2007 18:01:06 -0400, StargateFan
wrote:

I've been searching the archives but don't even know where to begin
because I don't know what function would be used in Excel to do this.
After some time, it just occurred to me that even conditional
formatting would work. An odd number of characters (I have code to
remove punctuation and spaces that I'd run on the text string first),
would make the cell turn red and even number of characters would turn
the cell background green because I need even-numbered text strings
and this would test for that before I go further.

I know something can be done because of this one message I managed to
find:
http://groups.google.ca/group/micros...a1f0de1cf496d2
but that's all there seems to be as Excel users are usu. concerned
with odd- and even-numbered rows rather than the length of a string of
text. tia! :oD

p.s., it wouldn't have to necessarily be conditional formatting, nor
would it have to be changing the cell background colour, btw. That
was just a thought. Anything that indicates when a text string is an
even number of characters in length would be fine.

(Also, the example in the URL above deals with a macro. Hoping I
didn't confuse the issue with that.)

Thanks! :oD

Everything gets counted, including spaces and it requires
the Analysis TookPak be enabled in Tools | Add-ins...
=ISEVEN(LEN(B3))
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"StargateFan"

wrote in message
On Fri, 06 Apr 2007 18:01:06 -0400, StargateFan
wrote:

p.s., it wouldn't have to necessarily be conditional formatting, nor
would it have to be changing the cell background colour, btw. That
was just a thought. Anything that indicates when a text string is an
even number of characters in length would be fine.

(Also, the example in the URL above deals with a macro. Hoping I
didn't confuse the issue with that.)
Thanks! :oD

On Fri, 6 Apr 2007 15:24:57 -0700, "Jim Cone"
wrote:

Everything gets counted, including spaces and it requires
the Analysis TookPak be enabled in Tools | Add-ins...
=ISEVEN(LEN(B3))

Any way to do this without an addin?? Thanks.

Hi StargateFen,

Alternatively, without the ATp addin, the formula:

=MOD(LEN(A1),2)=0

will return True or False or even and odd length strings.

If you wish to use Conduitional formatting, use the above
formala for one conditiona and use the following formula
for a second condition:

=MOD(LEN(A1),2)=1 (odd)


---
Regards,
Norman


"StargateFan" wrote in message
...
On Fri, 06 Apr 2007 18:01:06 -0400, StargateFan
wrote:

I've been searching the archives but don't even know where to begin
because I don't know what function would be used in Excel to do this.
After some time, it just occurred to me that even conditional
formatting would work. An odd number of characters (I have code to
remove punctuation and spaces that I'd run on the text string first),
would make the cell turn red and even number of characters would turn
the cell background green because I need even-numbered text strings
and this would test for that before I go further.

I know something can be done because of this one message I managed to
find:
http://groups.google.ca/group/micros...a1f0de1cf496d2
but that's all there seems to be as Excel users are usu. concerned
with odd- and even-numbered rows rather than the length of a string of
text. tia! :oD

p.s., it wouldn't have to necessarily be conditional formatting, nor
would it have to be changing the cell background colour, btw. That
was just a thought. Anything that indicates when a text string is an
even number of characters in length would be fine.

(Also, the example in the URL above deals with a macro. Hoping I
didn't confuse the issue with that.)

Thanks! :oD

will return True or False or even and odd length strings.

Should read:

will return True or False for even and odd length strings.



---
Regards,
Norman

=MOD(LEN(B3),2)=0
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

"StargateFan"

wrote in message
On Fri, 6 Apr 2007 15:24:57 -0700, "Jim Cone"
wrote:

Any way to do this without an addin?? Thanks.

On Fri, 6 Apr 2007 23:39:41 +0100, "Norman Jones"
wrote:

Hi StargateFen,

Alternatively, without the ATp addin, the formula:

=MOD(LEN(A1),2)=0

will return True or False or even and odd length strings.

If you wish to use Conduitional formatting, use the above
formala for one conditiona and use the following formula
for a second condition:

=MOD(LEN(A1),2)=1 (odd)

[snip]

This works a treat. I'm in process of completing first job using this
spreadsheet. But I ran into a finetuning question.

Can we add text to the conditional formatting somehow? The archives
don't yield anything. What I was thinking is something like this as
an idea:


=MOD(LEN(A1),2)=0"text: This works. You can use."
=MOD(LEN(A1),2)=1"text: This is odd-numbered and won't work. Find
something else."

I just chose silly messages but I hope purpose is understood, that the
user is actually notified whether something will work or not. Thanks!

Hi StargateFan,

'-------------------
This works a treat. I'm in process of completing first job using this
spreadsheet. But I ran into a finetuning question.

Can we add text to the conditional formatting somehow? The archives
don't yield anything. What I was thinking is something like this as
an idea:


=MOD(LEN(A1),2)=0"text: This works. You can use."
=MOD(LEN(A1),2)=1"text: This is odd-numbered and won't work. Find
something else."

I just chose silly messages but I hope purpose is understood, that the
user is actually notified whether something will work or not. Thanks!
'-------------------

I do not think that you can use Conditional Formatting to
return a message.

I no nothing of your application, but perhaps you could use
the Worksheet_Change event to report the problematic string?

Perhaps, try something like:

'=============
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Dim Rng2 As Range
Dim rCell As Range
Dim CMT As Comment

Set Rng = Me.Range("A1:A100") '<<==== CHANGE
Set Rng2 = Intersect(Rng, Target)

If Not Rng2 Is Nothing Then
For Each rCell In Rng2.Cells
With rCell
On Error Resume Next
.Comment.Delete
On Error GoTo 0
If Len(.Value) Mod 2 = 1 Then
Set CMT = .AddComment
With CMT
.Text "This is odd-numbered and won't work." _
& vbLf & " Find something else."
.Visible = True
End With
End If
End With
Next rCell
End If

End Sub
'<<=============

This is worksheet event code and should be pasted into
the worksheets's code module (not a standard module
and not the workbook's ThisWorkbook module):

Right-click the worksheet's tab
Select 'View Code' from the menu and paste the code.
Alt-F11 to return to Excel.


---
Regards,
Norman

I no nothing of your application, but perhaps you could use

I know nothing of your application, but perhaps you could use


---
Regards,
Norman

On Sun, 8 Apr 2007 12:13:53 +0100, "Norman Jones"
wrote:

Hi StargateFan,

'-------------------
This works a treat. I'm in process of completing first job using this
spreadsheet. But I ran into a finetuning question.

Can we add text to the conditional formatting somehow? The archives
don't yield anything. What I was thinking is something like this as
an idea:


=MOD(LEN(A1),2)=0"text: This works. You can use."
=MOD(LEN(A1),2)=1"text: This is odd-numbered and won't work. Find
something else."

I just chose silly messages but I hope purpose is understood, that the
user is actually notified whether something will work or not. Thanks!
'-------------------

I do not think that you can use Conditional Formatting to
return a message.

[snip]

Okay. Understood. I didn't think there was a way but what do I know?
<g

I did try putting a "checker" cell elsewhere in the sheet since only
one row and two columns are actively used in this sheet. It almost
works except I can't figure out how to "hide" the text that isn't
needed. I duplicated the above conditions except making the A1
absolute ($A$1) while keeping the same colours and text attributes in
the text that comes up in these other cells as A1. The difference is
that there is text revealed. One cell says:

"This text is not even-numbered, You cannot use it for a syllacrostic
puzzle."

and the other says:

"This text WORKS!! You can use it for a syllacrostic!"

The only problem I've run into is the condition of completely hiding
one cell when the other is valid. Both of them show up except that
one is formatted when the other isn't.

Both disappear when the cell is blank, however.

I think this will work. Since there are only 2 conditions needed for
these additional 2 cells, that leaves a third one free to see if I can
hide one text over the other. It might even be as easy as depending
on the colour of the other cell, maybe, since that changes. More
research in the archives ... <g