to VBA or Not to VBA - Coloured cell, sum IF

[MonkeyFace28]

Hi peeps - Happy New Year!

I was wondering - I need to do a lookup / or Match for cells that are coloured in red.

I have been advised the below is best - but I'm worried that it will only count the "numbers" in the cell which is coloured in.

What I actually want is to return a sum based on a column that isn't coloured in - column "O" has the conditional formatting but it has dates in the cells. The cell goes red when the date has passed today's date and doesn't have anything in the delivered column.

I need to sum the numbers in column "H" if the cell in column "O" is red.

is that possible with this VBA? or is there a simpler way to do it because I have't used VBA before really - but the website says copy and paste this...

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20​

<code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">Function</code> <code class="vb plain" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: black !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">ColorFunction(rColor </code><code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">As</code> <code class="vb plain" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: black !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">Range, rRange </code><code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">As</code> <code class="vb plain" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: black !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">Range, </code><code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">Optional</code> <code class="vb plain" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: black !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">SUM </code><code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">As</code> <code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">Boolean</code><code class="vb plain" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: black !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">)</code> <code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">Dim</code> <code class="vb plain" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: black !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">rCell </code><code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">As</code> <code class="vb plain" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: black !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">Range</code> <code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">Dim</code> <code class="vb plain" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: black !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">lCol </code><code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">As</code> <code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">Long</code> <code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">Dim</code> <code class="vb plain" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: black !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">vResult</code> <code class="vb plain" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: black !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">lCol = rColor.Interior.ColorIndex</code> <code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">If</code> <code class="vb plain" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: black !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">SUM = </code><code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">True</code> <code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">Then</code> <code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">For</code> <code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">Each</code> <code class="vb plain" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: black !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">rCell </code><code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">In</code> <code class="vb plain" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: black !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">rRange</code> <code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">If</code> <code class="vb plain" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: black !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">rCell.Interior.ColorIndex = lCol </code><code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">Then</code> <code class="vb plain" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: black !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">vResult = WorksheetFunction.SUM(rCell, vResult)</code> <code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">End</code> <code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">If</code> <code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">Next</code> <code class="vb plain" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: black !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">rCell</code> <code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">Else</code> <code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">For</code> <code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">Each</code> <code class="vb plain" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: black !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">rCell </code><code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">In</code> <code class="vb plain" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: black !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">rRange</code> <code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">If</code> <code class="vb plain" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: black !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">rCell.Interior.ColorIndex = lCol </code><code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">Then</code> <code class="vb plain" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: black !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">vResult = 1 + vResult</code> <code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">End</code> <code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">If</code> <code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">Next</code> <code class="vb plain" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: black !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">rCell</code> <code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">End</code> <code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">If</code> <code class="vb plain" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: black !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">ColorFunction = vResult</code> <code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">End</code> <code class="vb keyword" style="white-space: nowrap; padding: 0px !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-weight: bold !important; min-height: auto !important;">Function </code>

<tbody style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-size: 1em !important; min-height: auto !important;">
</tbody>

Any help greatly appreciated, Thanks in advance.

Steph

 

[NdNoviceHlp]

Hi Steph. This seems to work. HTH. Dave
Module code...

Function SumColor(Colored As Range, inputRange As Range, mycolor As Integer) As Double
Dim rCell As Range
Dim vResult
For Each rCell In Colored
If rCell.Interior.ColorIndex = mycolor Then
vResult = vResult + Cells(rCell.Row, inputRange.Column)
End If
Next rCell
SumColor = vResult
End Function

Worksheet formula example... (colors in o18, data in H1:H8, color index for red)

=sumColor(Sheet1!O1:O8,Sheet1!H1:H8,3)

 

[MonkeyFace28]

Thanks Dave, I haven't used VBA before so not sure if this is right... I opened a new module and just pasted your code in. then saved the spreadsheet. Next i put your formula in a free cell but when I hit enter it bought up another window as if to link a file to the cell? if I clicked close i get value error in the cell...

please help!? I'm completely confused

below is a copy of the data i'm using...

no of points	QUANTITY (Grams/Litres)	SETTING (months)	INSTALLED	DATE INSTALLED	REPLACEMENT DUE	Date Sent to Site	SUPPLIER ORDER DATE
2	125	12	Y	02/01/2017	02/01/2018		08/12/2017
2	60	12	Y	02/01/2017	02/01/2018		08/12/2017
2	125	12	Y	02/01/2017	02/01/2018		08/12/2017
2	60	12	Y	02/01/2017	02/01/2018		08/12/2017

<tbody>
</tbody>

1st column is "H" in the tab called Lubrication Schedule, last column is "O"

 

[NdNoviceHlp]

The code works for values in "H" and red color in "O". You place the sub in module code. U should be able to select a cell and then paste the formula into the formula bar and hit enter. The output should sum the values of "H" if "O" is red. The formula is for sheet1 and has "3" for a color index which is red. U can place this code in sheet code and run the sub to identify the colors U have in "O". If the output of this code is not 3, then U need to adjust the "3" to whatever the output is. HTH. Dave

Sub CheckInteriorColor()
Dim R As Range
'adjust sheet/range to suit
For Each R In Sheets("Sheet1").Range("o1:o8")
MsgBox "Cell : " & R.Address & " Interior color index: " & R.Interior.ColorIndex
Next R
End Sub

 

[MonkeyFace28]

The code works for values in "H" and red color in "O". You place the sub in module code. U should be able to select a cell and then paste the formula into the formula bar and hit enter. The output should sum the values of "H" if "O" is red. The formula is for sheet1 and has "3" for a color index which is red. U can place this code in sheet code and run the sub to identify the colors U have in "O". If the output of this code is not 3, then U need to adjust the "3" to whatever the output is. HTH. Dave

Sub CheckInteriorColor()
Dim R As Range
'adjust sheet/range to suit
For Each R In Sheets("Sheet1").Range("o1:o8")
MsgBox "Cell : " & R.Address & " Interior color index: " & R.Interior.ColorIndex
Next R
End Sub

Hey Dave,

I put this in and hit "run" but I got a script error... run time error 9 or something?

 

[NdNoviceHlp]

Is the sheet named Sheet1? Dave

 

[MonkeyFace28]

Is the sheet named Sheet1? Dave

they weren't but I renamed them to say sheet1 and 2. I put the colour code in and a window popped up to say "Cell: $O$1 interior colour index: -4142"

 

[NdNoviceHlp]

I think it returns -4142 when there is no interior colour index. Are there 2 relavent sheets? The purpose of the message box was just to inform U what interior color index for red was being used. This was so U could put that number in this worksheet formula...

=sumColor(Sheet1!O1:O8,Sheet1!H1:H8,put the color index U find here ie. 3)

Then use the previous function. Dave

 

[MonkeyFace28]

Hi Dave, I've managed to get it to work with no errors but now it's saying "0" as the result... but if I manually count I have 270. Thanks for all your help on this x

 

[MARK858]

That code doesn't work for conditional formatted cells, would it not be easier just to use a Sumif formula for >Today() ?