SUM CELL BORDERS? (and by colour too?)

[Neil Pearce]

Dear all,

I'm a quantity surveyor and have been creating a cost model for a generic
building. Using the formulas below and conditonal formatting I have managed
to create a range of building shapes that are visually displayed within a
workbook. The building's area and perimeter are automatically calculated.
This is very useful for costing external walls, floor & ceiling finishes,
roof, floor slab, foundations etc.

=CellColorIndex(Cell)
=CountByColor(Range,CellColorIndex,FALSE)

I would now like to expand my model and be able to draw onto my plan
internal partitons/walls by using the border options of the cells.

Is there a way to count/sum the cell border lines like the coloured cells
were counted above above? Further is there a way to count those border lines
based on their colour therby enabling differing wall make-ups to be portrayed
and calculated?


Thanking-you in advance,

Neil

 

[Jim Cone]

My recommendation is avoid "calculating" colored borders in your model.
Trying to use conditionally formatted borders could cause you to change your occupation. <g>

Even using "regular" colored borders could cause grief.
Borders between cells are common to both cells (except when they aren't).
Excel, however, does attempt to keep track of the "owner" cell for the border.
Unless all cells with borders were isolated from other cells, I would find another way.

You might want to play with the free "Determine Colors" add-in available as
a direct download from my Website. It is at the bottom of the products page.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"Neil Pearce"
wrote in message
Dear all,
I'm a quantity surveyor and have been creating a cost model for a generic
building. Using the formulas below and conditonal formatting I have managed
to create a range of building shapes that are visually displayed within a
workbook. The building's area and perimeter are automatically calculated.
This is very useful for costing external walls, floor & ceiling finishes,
roof, floor slab, foundations etc.

=CellColorIndex(Cell)
=CountByColor(Range,CellColorIndex,FALSE)

I would now like to expand my model and be able to draw onto my plan
internal partitons/walls by using the border options of the cells.

Is there a way to count/sum the cell border lines like the coloured cells
were counted above above? Further is there a way to count those border lines
based on their colour therby enabling differing wall make-ups to be portrayed
and calculated?
Thanking-you in advance,
Neil

 

[Neil Pearce]

Thanks for advice/warning Jim,

But to persevere...

The duplication of the borders is good as for the model of a building
skirtings and paint will have to be appplied to both sides of the wall. Not
so good though for calculating the actual length of internal partitions/walls!

Is it possilbe to tell each cell in a column to look only at the right side
border and sum at the bottom. Then tell each cell in a row to count only the
bottom border and sum at the side?

 

[Jim Cone]

Neil,
Each cell has a Borders property and the existence of a right edge border
can be returned with (vba code) ...
Range("B5").Borders(xlEdgeRight).LineStyle
(no line style means no border)
However, if the cell to the right has a left border then you get that cells
border linestyle returned.

Also, you can sum numeric contents of cells and you can sum the height
of various cells, but you can't sum borders. You could count the number
of cells with a right border however.

Note that the unit of measurement for cell width is not the same as for
it's height. (number of standard characters vs. points).
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




in message
Thanks for advice/warning Jim,
But to persevere...

The duplication of the borders is good as for the model of a building
skirtings and paint will have to be appplied to both sides of the wall. Not
so good though for calculating the actual length of internal partitions/walls!

Is it possilbe to tell each cell in a column to look only at the right side
border and sum at the bottom. Then tell each cell in a row to count only the
bottom border and sum at the side?

 

[Neil Pearce]

Hi Jim,

I'm Back from holidaying now so thought I'd probe some more...

Counting the number of cells with a right border is exactly what I'd like to
do. Preferably being able to amend the type or colour of border that the
count is searching for. Would the code for this be difficult for a novice
like myself?