# Solved: Excel 2007 - Colour Conditions



## calador (Mar 3, 2008)

Hi guys,

I would like to know if there is a way to define the colours within a column.

For example i have a set of data in column A. In that column I have highlighted some of it to signify something. Can I then SUM the whole lot of it WITHOUT the ones highlighted?

If that is possible, what about SUM the whole lot WITHOUT a specific highlighted group?

And if both of this are possible, what about those in different colour fonts?

Thanks


----------



## slurpee55 (Oct 20, 2004)

Are they all the same color, or are there a variety of colors? And if so, do the others that you want counted have no fill in them?


----------



## slurpee55 (Oct 20, 2004)

Also, did you color them or use conditional formatting to do so? And, is it the cell that is colored or the font?


----------



## Zack Barresse (Jul 25, 2004)

Hi,

I think you're going to need VBA for that. Do a internet search on "excel vba sum colors" (w/o the quotes) and you'll get a lot of hits. There is also a function that will return the color id of the color index for native sheet formatting AND conditional formatting...

http://www.vbaexpress.com/kb/getarticle.php?kb_id=190

You could use this in connection with a SUM or COUNT native function.

HTH


----------



## calador (Mar 3, 2008)

slurpee55 said:


> Are they all the same color, or are there a variety of colors? And if so, do the others that you want counted have no fill in them?


For the sake or arguement, they are in a variety of colors and yes the one counted have no fill in them.



slurpee55 said:


> Also, did you color them or use conditional formatting to do so? And, is it the cell that is colored or the font?


Err... Basically i want to know if i want to set conditions for the sum functions, what is the command for the color coding. So the criteria, may it be a combination of the following:
1) No fill or colored
2) Black Font or other colored Fonts
be satisfied.

Next in line would be the obvious one for newbies like me. How do you type the command out or is there a special Function for it e.g. =SUM(arg1, arg2)

Thanks

PS. haha.. i was just 2 minutes slower. Ill try it out and see if i can get anything out of that site that you just recommended. Well the first step will be to learn the language. I thought it was in C language but was very wrong.


----------



## Zack Barresse (Jul 25, 2004)

Well, the program was written in C++, but the language we are able to use for _our_ coding is VBA.


----------



## slurpee55 (Oct 20, 2004)

I had assumed, as firefytr stated, that VBA would probably be necessary for this, and I also knew from some research that having conditional formatting - as opposed to going in and setting the font or cell color using the formatting tools - is more difficult to deal with. 
I think it is because (and firefytr can correct me on this - and I am sure he will if I am wrong  ) if it is conditionally formatted, then the same conditions have to be written into the VBA code. At least, I would think that would make sense, so the code then can identify how they are colored.


----------



## Zack Barresse (Jul 25, 2004)

It is always much more difficult to get the conditional formatting color. The link for the KB posted above by byundt - a very, very highly skilled coder, and Microsoft MVP for Excel now (finally) - is best used on Excel 2003, although I believe I tested its basics in 2007 and it worked, but it will only work to a point, as the CF is so much more broad and expanse it is not really feasible to code for it.

You could, however, return the format type of the cell, so if you formatted it a certain way (i.e. custom format) you could use the CELL() function to retreive this data - no VBA required. Check it out here...

http://office.microsoft.com/en-us/excel/HP052042111033.aspx


----------



## slurpee55 (Oct 20, 2004)

Zack, could the poster also have - given the conditions that are used to designate which items to mark with a color - code that would both color those cells and sum the others?


----------



## slurpee55 (Oct 20, 2004)

calador said:


> Next in line would be the obvious one for newbies like me. How do you type the command out or is there a special Function for it e.g. =SUM(arg1, arg2)


To respond to this, we would need to know what your arguments are - I would expect you could probably set an IF statement to do a sum, based on what the arguments happen to be.


----------



## Zack Barresse (Jul 25, 2004)

slurpee55 said:


> Zack, could the poster also have - given the conditions that are used to designate which items to mark with a color - code that would both color those cells and sum the others?


Yeah. There is really no good way to span the link with this type of information. The structure is there to do it, but I don't think we'll see that until the next version of Excel, maybe the version after (hoping :up: ).

Personally, I would use VBA, but I love coding too. The other thing would be to in fact do just that, use formula to put the values in sheet cells so you have the data to manipulate when you want it. You could then, also, format on those cell(s).


----------



## slurpee55 (Oct 20, 2004)

If the formatting is based on, say, negative values, or values under/over a certain amount, then an IF statement may be all that is needed.


----------



## calador (Mar 3, 2008)

I wanted to send a PM to one of you instead of embarassing myself with my crude C programming.

Well this is what i want to achieve (somewhere along this lines)



> Function
> 
> =SUMcolour(Ccolour,Fcolour,a1:a2)
> 
> ...


_
Feel free to correct me. I have very little experience in it and know i am bad in it. Havent got the time to google out some of the premade functions. Will do so later but just for a starter, this is something i am looking for and wondering if something this simple is available outside.

Thanks_


----------



## slurpee55 (Oct 20, 2004)

As I understand, then, you just want to sum the numbers that are black and are in cells that are not colored - that is ignore the numbers that have filled cells (regardless of font color) and ignore the numbers that are not black (even if they are in non-filled cells).
Correct?


----------



## calador (Mar 3, 2008)

You could say so... (not quite in my current situation - almost opposite of that - if you can give me the answer for that, i can sum up the rest and minus it off from that answer) I basically want the option to do that. To choose whether i sum up the coloured cells or the non coloured cells or probably do a combination of that by font colours.


----------



## slurpee55 (Oct 20, 2004)

Per your code, I am assuming that you did not use conditional formatting, which makes things easier (if you don't know about conditional formatting, you might want to read this http://www.officearticles.com/excel/conditional_formatting_with_formulas_in_microsoft_excel.htm )
However, given that you have not used conditional formatting, this page will tell you *half of* how to do what you want.
http://www.ozgrid.com/VBA/sum-count-cells-by-color.htm


----------



## slurpee55 (Oct 20, 2004)

Oh, and read this page as well....
http://www.cpearson.com/excel/colors.htm
If you have questions about implementing this code, ask firefytr - he's the local Excel VBA guru.


----------



## calador (Mar 3, 2008)

Hmmm with the last post of yours, i think we can say its pretty much solved. I Will have to bookmark that page as it is very useful for any color related task. I definitely would like to steal some ideas to put it together myself but understanding VBA would seem to be the very first step for me. (as in language wise). Thanks Slurpee you have been a great help here.. with your minute-by-minute help


----------



## Zack Barresse (Jul 25, 2004)

Might see if this works for you...


```
Public Function SUMCOLOR(iColorIndex As Long, iFontIndex As Long, rCheck As Range) As Variant
    Dim zCell As Range, iTemp As Variant
    For Each zCell In rCheck.Cells
        If zCell.Interior.ColorIndex = iColorIndex Or zCell.Interior.ColorIndex = -4142 Then
            If zCell.Font.ColorIndex = iFontIndex Or zCell.Font.ColorIndex = -4105 Then
                If IsNumeric(zCell.Value) Then iTemp = iTemp + zCell.Value
            End If
        End If
    Next
    SUMCOLOR = 0
    If Not IsEmpty(iTemp) Then SUMCOLOR = iTemp
End Function
```
The strange minus numbers are cell default format value constants. For a list of [color] index values in Excel, look here...

http://www.vbaexpress.com/kb/getarticle.php?kb_id=206

HTH


----------



## slurpee55 (Oct 20, 2004)

Hey, is zCell short for ZackCell? Determined to get your name into the code, are you?


----------



## Zack Barresse (Jul 25, 2004)

ROFL!! I was thinking of defining a new type (ZACK) and substituting it for a range object, but I hesitated (probably not what the OP was looking for).


----------



## slurpee55 (Oct 20, 2004)

How about you write up a very useful UDF of some sort and name it Zack?


----------



## Zack Barresse (Jul 25, 2004)

Well, maybe my own type...


```
Option Explicit

Public Type ZACK
    zCoolness As Long
    zAttitude As Long
    zCell As Range
    zMood As Double
End Type

Sub testingZACK()
    Dim rCell As ZACK, sMsg As String
    Const NL As String = vbNewLine
    Set rCell.zCell = ActiveCell
    rCell.zCell.Clear
    rCell.zCell.Font.ColorIndex = 32
    rCell.zCell.Font.Bold = True
    rCell.zCell.Interior.ColorIndex = 4
    rCell.zCell.Value = "ZACK TEST"
    rCell.zCell.EntireColumn.AutoFit
    rCell.zAttitude = 1
    rCell.zCoolness = 10
    rCell.zMood = 7
    sMsg = rCell.zCell.Address(0, 0) & ": " & rCell.zCell.Value & NL
    sMsg = sMsg & "Attitude: |" & String(rCell.zAttitude, "-") & String(10 - rCell.zAttitude, " ") & "|" & NL
    sMsg = sMsg & "Coolness: |" & String(rCell.zCoolness, "-") & String(10 - rCell.zCoolness, " ") & "|" & NL
    sMsg = sMsg & "Mood: |" & String(rCell.zMood, "-") & String(10 - rCell.zMood, " ") & "|" & NL & NL
    sMsg = sMsg & "This is today's reading for Zack."
    MsgBox sMsg, vbInformation, "ZACK"
End Sub
```


----------



## slurpee55 (Oct 20, 2004)

Oh grief! You could have at least set the readings to vary!!! LOL!!!
But I will keep it - who knows when that may come in handy?


----------



## exegete (Oct 26, 2005)

Good thing it wasn't me, I would have had to add another parameter to the Type:

```
rBelly As Double
```
Whew!


----------



## Zack Barresse (Jul 25, 2004)

LOL! Which reminds me, I forgot _zMusculature_ as Double. ROFL!!


----------



## slurpee55 (Oct 20, 2004)

Oh, gag...hmmm, what else could someone have to set...
Hair=100?
Sparetime=0?


----------



## calador (Mar 3, 2008)

Lol.. this has become a very fun thread though i really need to know the language in order to understand a thing. Hmm was going to Set this thread as solved, How do i do that?


----------



## calador (Mar 3, 2008)

Could you like in short explain how to work this out?

=SumColor(27,1,a1:a10)
Assuming i have 10 cells 8 of which are highlighted in yellow 2 are not highlighted, all are in black font (1)
I keep getting wrong answers..the values i put in all 10 cells are 1. I should be getting 8 as my answer but i keep getting 2. It looks like the will only sum up the non highlighted cells - which IS good but tot could bring in more flexibility to prolly sum up the yellow cells instead of the non-colour too for example.


----------



## Zack Barresse (Jul 25, 2004)

calador, can you post the small sample you were working with? I ask you to do that, as opposed to us creating one, because formats are so precarious, it's better to work with your actual sample/data than try to re-create it (we would probably screw it up).


----------



## calador (Mar 3, 2008)

Well here are 2 examples i am playing with your function. See if you can make any sense out of it. 

PLEASE please do scan it before opening it. I am not the jerk who will do anything with it but i dont want to be blamed for anything. Open at your own risk. (just to cover my ***)

Thanks guys (oh yeah.. i have seen and read somewhere, where they use a special program that scans a file using at least 10 spyware/malware detectors and it outputs a pass/fail msg of all those results. Do you have any idea what it is?)


----------



## slurpee55 (Oct 20, 2004)

For multiple AV scans, try either
http://www.virustotal.com/
or
http://virusscan.jotti.org/
The former uses more AV programs, but if you are seriously in doubt about a file, it wouldn't hurt to use both.


----------



## Zack Barresse (Jul 25, 2004)

Okay, changed it up a bit, put the range first and changed the next two values to optional, and a couple of checks in the code to make sure and not count non-formatted cells when a format is specified. And here are a couple of other functions you can use to get the colorindex of the cell and the font...


```
Public Function SUMCOLOR(rCheck As Range, _
                         Optional iColorIndex As Long = -4142, _
                         Optional iFontIndex As Long = -4105) _
                         As Variant
    Dim zCell As Range, iTemp As Variant
    For Each zCell In rCheck.Cells
        If zCell.Interior.ColorIndex = iColorIndex Or zCell.Interior.ColorIndex = -4142 Then
            If iColorIndex <> zCell.Interior.ColorIndex Then GoTo Skip_zCell
            If zCell.Font.ColorIndex = iFontIndex Or zCell.Font.ColorIndex = -4105 Then
                If iFontIndex <> zCell.Font.ColorIndex Then GoTo Skip_zCell
                If IsNumeric(zCell.Value) Then iTemp = iTemp + zCell.Value
            End If
        End If
Skip_zCell:
    Next
    SUMCOLOR = 0
    If Not IsEmpty(iTemp) Then SUMCOLOR = iTemp
End Function

Function GETINDEXCOLOR(rCheck As Range) As Long
    If rCheck.Cells.Count > 1 Then Exit Function
    GETINDEXCOLOR = rCheck.Interior.ColorIndex
End Function

Function GETFONTCOLOR(rCheck As Range) As Long
    If rCheck.Cells.Count > 1 Then Exit Function
    GETFONTCOLOR = rCheck.Font.ColorIndex
End Function
```
Seems to work for me. Here are the formulas I used...

*Top Range:*
Sum Yellow cells:
=SUMCOLOR(B3:B12,6)

Blue cells:
=SUMCOLOR(B3:B12,5,3)
(The 3 at the end is for the font color)

Non-coloured cells:
=SUMCOLOR(B3:B12)

*Bottom Range:*
Yellow Cells:
=SUMCOLOR(B21:C30,6)

Blue Cells:
=SUMCOLOR(B21:C30,5,3)

Non-coloured cells:
=SUMCOLOR(B21:C30)

Let us know how this works for you.

HTH


----------



## slurpee55 (Oct 20, 2004)

Works like a charm Zack.


----------



## calador (Mar 3, 2008)

That was quick!.. saved me a load of hours trying to figure out how it works.. I think this is a great tool for people like me who loves to highlight stuffs to remind me things. I think this is worth putting up a custom function thread and stick this in. Works like a charm for me


----------



## slurpee55 (Oct 20, 2004)

calador, please use the thread tools at the top of the page to mark this thread as solved.


----------

