Count Highlighted Cells

    Hi, I am trying to get a macro that will count all the highlighted cells in Div. 1,Div 2, Div. 3 and Div. 4 and put the total in Cell AD 43 in Div,4 . The cells are all highlighted by conditional formatting. I have searched the internet all day and can't find anything that works. Even if I could get the total for each sheet it would help. Thanks.

    Hi,


    Try this code in the sheet you posted.



    Let me know if it does what you are after.

    Justin

    Fantastic! Thanks Justin.

    Hi Roy, Hampton,


    I did not test the function but I expect it will not count cells that have been formatted using conditional format, to modify the function to count these cells insert DisplayFormat whenever referencing the colorindex, eg:


    change

    rCl.Interior.ColorIndex = lCol

    to

    rCl.DisplayFormat.Interior.ColorIndex = lCol


    Without the displayformat the code only counts cells that have had the background color changed using fill-color.


    HTH

    Justin

    Thanks Justin, I will try that. What you gave me does seem to work though.

    The function would be a better solution, rather than having to run the macro every time. Either that or writing the code into the sheetchange event so it updates whenever the conditional format is activated. But if the macro does what you need all is good :)

    Quote from Justin Doward

    The function would be a better solution, rather than having to run the macro every time. Either that or writing the code into the sheetchange event so it updates whenever the conditional format is activated. But if the macro does what you need all is good :)

    Hi Justin, I went back and checked and you did have the correction for the DisplayFormat in the macro you sent. However, do you have an example of the function I could use so not have to use the macro? Thanks.

    Quote

    Note that the DisplayFormat property does not work in User Defined Functions (UDF). For example, on a worksheet function that returns the interior color of a cell, you use a line similar to: Range(n).DisplayFormat.Interior.ColorIndex. When the worksheet function executes, it returns a #VALUE! error.

    See this article.


    I would suggest that you use SUMIF based on the CONDITIONAL FORMATTING conditions

    I've had formulas in Div 4, you could do the same in the other sheets then summarise the results.

    Quote from royUK

    I've had formulas in Div 4, you could do the same in the other sheets then summarise the results.

    Hi, Roy, thanks for your help. I am not following the logic of your formulas. It appears you are summing the cells with a 6, 7 and then all other cells. I don't see how that will give me a count of the green highlighted cells. Help.

    Hi Hampton,


    I got this to work:

    Place a workbook_SheetChange event in the workbook code sheet.


    Then the sub in a module.



    I am sure there must be a more efficient way to check if iNts is empty but I kept getting a global method fail.


    Let me know how it goes.

    Justin

    You can replace the code in the workbook_SheetChange event with:


    Code
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range)
If Not Intersect(Sh.Range(target.Address), Sh.Range("C7:U41")) Is Nothing Then
Application.ScreenUpdating = False
Call background_color_count
Application.ScreenUpdating = True
End If
End Sub
    Quote from Justin Doward

    You can replace the code in the workbook_SheetChange event with:


    Code
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range)
If Not Intersect(Sh.Range(target.Address), Sh.Range("C7:U41")) Is Nothing Then
Application.ScreenUpdating = False
Call background_color_count
Application.ScreenUpdating = True
End If
End Sub

    Thanks again Justin, put it in and total updates automatically. Appreciate your help.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login