What IF statement required

[dr4ke]

Good afternoon Forum,

I'm currently looking for a formula to capture a name and a fill colour and transfer it into a number.

To elaborate on what i'm asking for i've attached an example spreadsheet. What I require the IF statement (or other statement if required) to provide is to look at Columns C & D and report back the following;

IF[Column C matches the text "Person 1] then [Look at column D] If [Column D has a Red Fill] then [Add all red cells for this person and display in G6]

And

IF[Column C matches the text "Person 1] then [Look at column E] If [Column E has a Red Fill] then [Add all red cells for this person and display in G7]



I will then have a table (In this example Cells G6 & G7) which reports how many Red cells are highlighted for each individual person for both columns separately.

I know this sounds like a very complicated formula to make, however if anyone is up to the task i'd be endlessly grateful. If you need further information please ask as i'm sure I may not have explained as well as I could have

Many thanks,
Dr4ke

[OTPM]

Hi
Unfortunately you cannot do what you want with an If formula. It would have to be done using VBA.
Tony(OTPM)

[dr4ke]

Originally Posted by OTPM

Hi
Unfortunately you cannot do what you want with an If formula. It would have to be done using VBA.
Tony(OTPM)

Hi Tony,

Thanks for your response. How would I do this through VBA?

Regards,
Simon

[Catalin.B]

May i sugest using a User Defined Function, to get cell or font color, as in example attached, then use this new functions in your formulas.

[dr4ke]

Originally Posted by Catalin.B

May i sugest using a User Defined Function, to get cell or font color, as in example attached, then use this new functions in your formulas.

Thank you for your response and the example, however i'm not amazingly technical with Excel and the forumla's have confused me a little bit. Could you perhaps explain what you have done so I could adapt it to my spreadsheet?

Thanks in advance,
Dr4ke

[Catalin.B]

in the new attached example, in columns K and L, i used the UDF to get the cell color.
=IFERROR(IF(SEARCH("Person 1";C6)>0;SUMIFBYCOLOR(D6:E6;3;D6:E6;FALSE);0);0)-this formula in column G searches first cell for "Person 1" and sums the green range if the cell colour is 3 (means red to excel) all results are totalised in cell G6 for person 1.
Same for person 2 in column H...

This formula in cell J2:
=COUNTIFS(C$6:C$20;C$6;L$6:L$20;"=3")+COUNTIFS(C$6:C$20;C$6;K$6:K$20;"=3")
counts how many cells are red (=3) for C$6 (person 1), (depending to ranges L$6:L$20 and K$6:K$20)

This formula in cell J3:
=COUNTIFS(C$6:C$20;C$7;L$6:L$20;"=3")+COUNTIFS(C$6:C$20;C$7;K$6:K$20;"=3")
counts how many cells are red (=3) for C$7 (person 2), (depending to ranges L$6:L$20 and K$6:K$20). In the example, the result is 0, because there are no red cells for person 2...
but if you change =3 to =43, in J3 formula, you will get the number of cells that are color 43 for person 2... and so on, ....

This formula will get the cell color:
=CELLCOLORINDEX(D6;FALSE)
This formula will get the font color used in that cell:
=CELLCOLORINDEX(D6;TRUE)

NOTE: When you change the background or font color of a cell, Excel does not consider this to be changing the value of the cell. Therefore, it will not recalculate the worksheet, nor will it trigger a Worksheet_Change event procedure. This means that the values returned by these functions may not be correct immediately after you change the color of a cell. They will not return an updated value until you recalculate the worksheet by pressing ALT+F9 or by changing the actual value of a cell.
NOTE: These functions will not detect colors that are applied by Conditional Formatting.


If you want to use a UDF in more than one workbook, you can save your functions in your own custom add-in. Simply save your excel file that contains your VBA functions as an add-in file (.xla). Then load the add-in (Tools > Add-Ins...). Warning! Be careful about using custom functions in spreadsheets that you need to share with others. If they don't have your add-in, the functions will not work when they use the spreadsheet.

[OTPM]

Hi Catalin
I have looked at this solution using Excel 2010 in order to build on my own knowledge. However as soon as I enable the worksheet all the formulae fail to work and I get #NAME errors in Columns J, K and L, and 00:00:00 in Columns G and H.
Presumably this is because the Function VBA code is not contained in the spreadsheet sample you provided?
Please advise.
Thanks
Tony(OTPM)

[Catalin.B]

there is the functions codes in the example from previous post...
check macro security: macro should be enabled for these functions to work

If you want to use a UDF in more than one workbook, you can save your functions in your own custom add-in. Simply save your excel file that contains your VBA functions as an add-in file (.xla). Then load the add-in (Tools > Add-Ins...). Warning! Be careful about using custom functions in spreadsheets that you need to share with others. If they don't have your add-in, the functions will not work when they use the spreadsheet.

anyway, the functions are:

Function CellColorIndex(InRange As Range, Optional _
    OfText As Boolean = False) As Integer

Application.Volatile True
If OfText = True Then
    CellColorIndex = InRange(1, 1).Font.ColorIndex
Else
    CellColorIndex = InRange(1, 1).Interior.ColorIndex
End If

End Function
Function SumIfByColor(InRange As Range, _
    WhatColorIndex As Integer, SumRange As Range, _
    Optional OfText As Boolean = False) As Variant

Dim OK As Boolean
Dim Ndx As Long

Application.Volatile True

If (InRange.Rows.Count <> SumRange.Rows.Count) Or _
    (InRange.Columns.Count <> SumRange.Columns.Count) Then
    SumIfByColor = CVErr(xlErrRef)
    Exit Function
End If

For Ndx = 1 To InRange.Cells.Count
    If OfText = True Then
        OK = (InRange.Cells(Ndx).Font.ColorIndex = WhatColorIndex)
    Else
        OK = (InRange.Cells(Ndx).Interior.ColorIndex = WhatColorIndex)
    End If
    If OK And IsNumeric(SumRange.Cells(Ndx).Value) Then
        SumIfByColor = SumIfByColor + SumRange.Cells(Ndx).Value
    End If
Next Ndx

End Function
Function SumByColor(InRange As Range, WhatColorIndex As Integer, _
    Optional OfText As Boolean = False) As Double
'
' This function return the SUM of the values of cells in
' InRange with a background color, or if OfText is True a
' font color, equal to WhatColorIndex.
'
Dim Rng As Range
Dim OK As Boolean

Application.Volatile True
For Each Rng In InRange.Cells
    If OfText = True Then
        OK = (Rng.Font.ColorIndex = WhatColorIndex)
    Else
        OK = (Rng.Interior.ColorIndex = WhatColorIndex)
    End If
    If OK And IsNumeric(Rng.Value) Then
        SumByColor = SumByColor + Rng.Value
    End If
Next Rng

End Function

Another reason for a function not to work, is if there are features in this macro created in office 2007 no longer supported in office 2010, which i think it's not the case here...

[Catalin.B]

Or, the formula in those cells refers to my User Defined Formulas Add-in saved in C:\Users\Catalin\AppData\Roaming\Microsoft\Program e de completare
If the formula looks like this:
=C:\Users\Catalin\AppData\Roaming\Microsoft\Program e de completare\formule personalizate.xlam!CELLCOLORINDEX(D6;TRUE)

means that the worksheet formula uses the function stored in my computer path, not the VBA code of this workbook... (sorry for that )

for the functions to work from this workbook code, just remove the red path from cells formulas:
=CELLCOLORINDEX(D6;TRUE)