count colours with IF formula / VBA

[ndendrinos]

Hello All,
Read a lot on functions and formulas to return the numbered of coloured cell in a range.
What I need over and above that is the following:

example : In range A1:G1
A1 , C1 have a yellow fill.
The count in H1 should return 2
Now the IF and it goes like this :
If H1 value = 2 then I2 = J5
ElseIf If H1 value = 3 then I2 = J6 etc...

There are 6 IF's to each range

Thank you

 

[acw]

Hi

try
I2: =offset(J3,h1,0)

Tony

 

[Fitzhay]

Hi

This may help

http://www.cpearson.com/excel/colors.htm


Regards

 

[stanleydgromjr]

CountByColor Function and If Statement

ndendrinos,

Based on your requirements:

Test data:

Count By Color.xls
	A	B	C	D	E	F	G	H	I	J	K
1								2		SearchColor
2									J5ans2
3
4										J4ans1
5										J5ans2
6										J6ans3
7										J7ans4
8										J8ans5
9										J9ans6
10										J10ans7
11
Sheet1

Press and hold down the 'ALT' key, and press the 'F11' key.

Insert a Module in your VBAProject, Microsoft Excel Objects

Copy the below code, and paste it into the Module1.

Option Explicit


Function CountByColor(CellColor As Range, SumRange As Range)
'
' SumByColor Function
' VBA and Macros for Microsoft Excel by Bill Jelen "Mr.Excel"
' Page 84
'
' Modified 02/04/2007 by Stanley D. Grom, Jr.
'
    Dim myCell As Range
    Dim iCol As Integer
    Dim myTotal
    iCol = CellColor.Interior.ColorIndex
    For Each myCell In SumRange
        If myCell.Interior.ColorIndex = iCol Then
            myTotal = myTotal + 1
        End If
    Next myCell
    CountByColor = myTotal
End Function

The IF statments in cell I2:
=IF(H1=1,J4,IF(H1=2,J5,IF(H1=3,J6,IF(H1=4,J7,IF(H1=5,J8,IF(H1=6,J9,IF(H1=7,J10,0)))))))

Have a great day,
Stan

 

[ndendrinos]

Fitzhay thank you for the link .. I will have a look at it

Tony, Stanley great formulas both work.
The problem I still have is that I need to fully automate it and the way I see it each time the number of yellow cells change in the range I have to reenter the formula in H2 manually.
Is there a way to fix this?

Thank you all for your kind help
Nick

Could add this I guess

Sub enterformulainh2()
    Range("H1").Select
    ActiveCell.FormulaR1C1 = _
        "=CountByColor(RC[2],RC[-7]:RC[-1])"
    [J2].Activate
End Sub

 

[stanleydgromjr]

Nick,

What changes the range A1:G1? A download or a manual change?

Could the change to A1:G1 also change H1? You should not have to enter the formula into cell H1 whenever range A1:G1 changes.

We could incorporate a Worksheet_Change event to triger a fix for the above.

Have a great day,
Stan

 

[ndendrinos]

We could incorporate a Worksheet_Change event to triger a fix for the above.

Hello Stan and thank you for the follow up.
The above is a no no according to the experts ... I did try it and it behaves funny.
A macro puts yellow background into the cells so like you suggest it I just added to the routine to enter the formula in H1 so all is well for now.

Best regards and thanks again
Nick