Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting by colour dilemma!!
Hi Dave, The code below is as it appears below nothing has been changed, th formulae are in B5 and B6 respectively B5 returns the count by colou for column A just fine but the CntByColor returns a value of 0 but o step thru does not use the CntbyColor function it uses the first lines and then uses the Count By Color its wierd! Any suggestions?.....the functions are caled upon by the Auto_open Simon Sub Auto_open() Dim ccount As Integer Dim cccount As Integer Application.DisplayAlerts = False Application.DisplayFormulaBar = False Range("B5").Select ActiveCell.FormulaR1C1 "=COUNTBYCOLOR(R[9]C[-1]:R[484]C[-1],38,FALSE)" Range("B7").Select Range("d14").Select ccount = Range("b5") Range("B6").Select ActiveCell.FormulaR1C1 = "=CntByColor(R[8]C[2]:R[485]C[33],38,FALSE)" 'Range("B7").Select 'Range("d14").Select Range("B6") = cccount Worksheets("holidays").Visible = True Worksheets("Holiday Count").Visible = True Worksheets("Xtra's & Count").Visible = True Sheets("holidays").Activate MsgBox "There Are " & ccount & " Holiday Clashes" & Chr(13) & " There Have Been " & cccount & " accomodations", vbOKOnly, "Clash Count" End Sub Function CountByColor(InRange As Range, WhatColorIndex As Integer Optional OfText As Boolean = False) As Long Dim Rng As Range Application.Volatile True For Each Rng In InRange.Cells If IsDate(Rng) Then If OfText = True Then CountByColor = CountByColor - _ (Rng.Font.ColorIndex = WhatColorIndex) Else CountByColor = CountByColor - _ (Rng.Interior.ColorIndex = WhatColorIndex) End If End If Next Rng End Function Function CntByColor(InRange As Range, WhatColorIndex As Integer, _ Optional OfText As Boolean = False) As Long Dim Rng1 As Range Application.Volatile True Rng1 = Range("D14:AI491") For Each Rng1 In InRange.Cells If Rng1.Value = "#N/A" Then 'do nothing Else If IsNumber = True Then CntByColor = CntByColor - _ (Rng.Font.ColorIndex = WhatColorIndex) Else CntByColor = CntByColor - _ (Rng.Interior.ColorIndex = WhatColorIndex) End If End If Next Rng1 End Functio -- Simon Lloy ----------------------------------------------------------------------- Simon Lloyd's Profile: http://www.excelforum.com/member.php...nfo&userid=670 View this thread: http://www.excelforum.com/showthread.php?threadid=27633 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting by colour dilemma!! | Excel Programming | |||
Counting by colour dilemma!! | Excel Programming | |||
Counting by colour dilemma!! | Excel Programming | |||
Counting by colour dilemma!! | Excel Programming | |||
Counting Function Dilemma | Excel Worksheet Functions |