How can I test a range of cells for the presence of a border?

[PFB]

Excel 2007 / Vista
I have a scheduling worksheet showing individuals' names indicating in which
area they will work on each day of the month. A subgroup of workers has
their cumulative shifts assigned first to a "place holder name", and then
each of those shifts is assigned to one of five individuals in the subgroup.
As the individual assignments are made the cell changes color based on
conditional fomatting for each name. However, I need to keep a count of the
cells in each column that orriginally displayed the "place holder name". I
thought I would assign borders to those cells before changing them to the
individuals' names and then COUNTIF() the number of cells in each column that
had borders (unchanged), but I can't find any way to count the number of
cells in a range that have a particular border .

 

[Sheeloo]

If these five individuals are assigned only against the 'place holder' cells
then you need to count only for cells containing these names...

Since you have only five names you can use Sumproduct like the one below;
=SUMPRODUCT(--(A1:A100={"Name A","Name B","Name C","Name D","Name E"}))

 

[PFB]

Thanks for the suggestion. Unfortunately the "place holder" cells are
sometimes traded away to people outside of the 5 person share group and there
are 30 people in total. I still need to be able to count the number of cell
originally displaying the "place holder" even if some finally dislplay a
non-share-group individual.

 

[Spiky]

Excel 2007 / Vista
I have a scheduling worksheet showing individuals' names indicating in which
area they will work on each day of the month.  A subgroup of workers has
their cumulative shifts assigned first to a "place holder name", and then
each of those shifts is assigned to one of five individuals in the subgroup.  
As the individual assignments are made the cell changes color based on
conditional fomatting for each name.  However, I need to keep a count of the
cells in each column that orriginally displayed the "place holder name".  I
thought I would assign borders to those cells before changing them to the
individuals' names and then COUNTIF() the number of cells in each column that
had borders (unchanged), but I can't find any way to count the number of
cells in a range that have a particular border .

You'll need a UDF to access formats in a formula, at least for more
than one cell. There is one in the morefunc group (search for it at
download.com) that would work. Or someone with the skills could write
a UDF. If you were to download this UDF and use it, this formula
should work to count all cells with a bottom border:
=SUMPRODUCT(--(XLM.GET.CELL(12,A2:A100)>0))

I don't have xl2007, and it has apparently changed VBA a bit, so I
can't vouch for this working in that version.

 

[Ashish Mathur]

Hi,

This does not seem to work in Excel 2003 either. What am I doing wrong.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Spiky]

What's happening? It works for me. I guess the most obvious issues
might be:

-Make sure you have installed morefunc.
-Typos.
-The "12" argument of XLM.GET.CELL checks the bottom border. A border
could be a top border and appear like the bottom of the cell above,
but would not be counted. There are arguments to check all 4 sides of
cells for borders, the formula can be modified to suit.
-You do have to force a manual calculation for this. Simply changing
cells in the specified array will not re-calc this function, so hit
F9. This is true of Excel's built-in CELL function, too, IIRC.

 

[PFB]

Thanks! This worked perfecly. I am a long-time but basic Excel user so I
had no idea that there was a source for UDF (which I had never heard of
anyway). It took me a while to find them - hence the late return post.
Interestingly, the new functions/macros only appear in the Fx drop-down
listing on the ribbon, but not in the drop-down lists associated with the
various category icons on the ribbon to the left of the Fx icon.

 

[PFB]

This answer addressed another problem I was having, but I need some
clarification. I was able to make the SUMPRODUCT formula work just as you
said until I changed the range to A9:U9 - Then I got the dreaded #N/A. As
long as I left the range in a single column I was OK but when I changed it to
a row - no luck.

What am I doing wrong?