Help req: how to conditional count with words in cells based on their formatting

[babs]

I have a text based spread sheet that counts the number of words at the end
of each row. I want to be able to bold selected words and have these
excluded from the count at the end of the row e.g.

frog cat dog (If cat is bolded then the count at the end of the
row is 2 not 3).

Thanks in advance.

Babs

 

[Jim Cone]

That will require VBA code.

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"babs"
wrote in message
I have a text based spread sheet that counts the number of words at the end
of each row. I want to be able to bold selected words and have these
excluded from the count at the end of the row e.g.

frog cat dog (If cat is bolded then the count at the end of the
row is 2 not 3).

Thanks in advance.
Babs

 

[T. Valko]

Why is a cell bold?

Can you write a formula based on *why* the cell is bold?

 

[babs]

The spreadsheet is a record of students spelling results each week. I want
to be be able to bold (or change the colour) of words that are incorrect and
have these words excluded from the total at the end of the row.

Babs

 

[Pete_UK]

Chip Pearson has some notes on counting by colour here:

http://www.cpearson.com/excel/colors.htm

Perhaps you can make use of some of his routines.

Hope this helps.

Pete

 

[babs]

Thanks for your help

On the site I found the following code which look like it will work but I am
new to excel and unsure how to insert the code.
Any chance that someone could insert it into an excel chart for me to show
me how to do it.
I am a teacher and would appreciate any help.

Thanks in advance

Babs

Counting Cells With A Specific Color
The following function will return the number of cells in a range that have
either an Interior (background) or Font of a specified color. InRange is
the range of cells to examine, WhatColorIndex is the ColorIndex value to
count, and OfText indicates whether to return the ColorIndex of the Font
(if OfText is True) or the Interior (if OfText is False or omitted).

Function CountByColor(InRange As Range, _
WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Long
'
' This function return the number of cells in InRange with
' a background color, or if OfText is True a font color,
' equal to WhatColorIndex.
'
Dim Rng As Range
Application.Volatile True

For Each Rng In InRange.Cells
If OfText = True Then
CountByColor = CountByColor - _
(Rng.Font.ColorIndex = WhatColorIndex)
Else
CountByColor = CountByColor - _
(Rng.Interior.ColorIndex = WhatColorIndex)
End If
Next Rng

End Function




Chip Pearson has some notes on counting by colour here:

http://www.cpearson.com/excel/colors.htm

Perhaps you can make use of some of his routines.

Hope this helps.

Pete

 

[T. Valko]

Ok, here's some ideas on how to make this all happen automatically.

You have a list of the correctly spelled words as key.
You can use conditional formatting to automatically highlight cells that
contain misspelled words.
You can count the misspelled words by checking them against the key.

 

[Pete_UK]

If you are new to macros you might find this site by David MacRitchie
to be helpful:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Hope this helps.

Pete

 

[babs]

Thanks for your help.

I have worked out how to use conditional formatting to automatically change
the colour of cells that contain misspelled words, however I don't know how
to count the misspelled words by checking them against the key.

Would you mind if I send you a small excel sample spreadsheet to look at?

Would really appreciate your help.

Babs

 

[Ken Johnson]

Hi Babs,

Last week I got your email address from your profile then I sent you a
workbook with Chip Pearson's UDF in place. Unfortunately there was
some sort of problem and the message couldn't be delivered.

If Biff doesn't notice your last message feel free to send me a copy
of your sample workbook to have a look at and I'll see if I can get it
working.

Ken Johnson

 

[T. Valko]

Try this:

A1:E1 = words typed in by students

werd1...word2...word3...wurd4...word5

The word key is in the range J1:J5

word1...word2...word3...word4...word5

There are 2 misspelled words.

=SUMPRODUCT(--(ISNA(MATCH(A1:E1,J1:J5,0))))

=2

Empty cells will be counted as misspelled words.

You can prevent the count from happening until *all* cells have been filled:

=IF(COUNTA(A1:E1)<5,"",SUMPRODUCT(--(ISNA(MATCH(A1:E1,J1:J5,0)))))

You can also delay the formatting in the same manner so the student won't
see any incorrect/correct reaults until *all* cells have been filled.

 

[Ken Johnson]

Hi Biff,
Babs sent me a sample workbook with a key row in row 6 with correctly
spelled words in F6:I6.
Student names in column D (D810) and each of the three student's
spellings of the key words along their row in columns F:I.
Column K is where the formula goes for a count of each student's
correct spellings.

I filled the following down from K8...

=SUMPRODUCT(--(F8:I8=$F$6:$I$6))

which seems OK.
Babs was happy with it too.

Ken Johnson

 

[T. Valko]

Good deal. Thanks for letting me know.