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.
Count Highlighted Cells
- hamptongolfer11
- Thread is marked as Resolved.
-
-
-
Hi,
Try this code in the sheet you posted.
Code
Display MoreSub background_color_count() Dim x As Long, c As Range x = 0 For Each c In Sheet1.Range("C7:U41") If c.DisplayFormat.Interior.Color <> 16777164 And c.DisplayFormat.Interior.Color <> 16777215 Then x = x + 1 Next c For Each c In Sheet2.Range("C7:U41") If c.DisplayFormat.Interior.Color <> 16777164 And c.DisplayFormat.Interior.Color <> 16777215 Then x = x + 1 Next c For Each c In Sheet3.Range("C7:U41") If c.DisplayFormat.Interior.Color <> 16777164 And c.DisplayFormat.Interior.Color <> 16777215 Then x = x + 1 Next c For Each c In Sheet4.Range("C7:U41") If c.DisplayFormat.Interior.Color <> 16777164 And c.DisplayFormat.Interior.Color <> 16777215 Then x = x + 1 Next c Sheet4.Range("AD43").Value2 = x End Sub
Let me know if it does what you are after.
Justin
-
Hi,
Try this code in the sheet you posted.
Code
Display MoreSub background_color_count() Dim x As Long, c As Range x = 0 For Each c In Sheet1.Range("C7:U41") If c.DisplayFormat.Interior.Color <> 16777164 And c.DisplayFormat.Interior.Color <> 16777215 Then x = x + 1 Next c For Each c In Sheet2.Range("C7:U41") If c.DisplayFormat.Interior.Color <> 16777164 And c.DisplayFormat.Interior.Color <> 16777215 Then x = x + 1 Next c For Each c In Sheet3.Range("C7:U41") If c.DisplayFormat.Interior.Color <> 16777164 And c.DisplayFormat.Interior.Color <> 16777215 Then x = x + 1 Next c For Each c In Sheet4.Range("C7:U41") If c.DisplayFormat.Interior.Color <> 16777164 And c.DisplayFormat.Interior.Color <> 16777215 Then x = x + 1 Next c Sheet4.Range("AD43").Value2 = x End Sub
Let me know if it does what you are after.
Justin
Fantastic! Thanks Justin.
-
This UDF might be useful. You can sum, count and average coloured cells
-
This UDF might be useful. You can sum, count and average coloured cells
Thanks Roy, I will give it a try. This is such a great community, I have learned a lot. Thanks everyone.
-
-
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
-
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
-
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.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.
-
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.
-
I based it on the Conditional Formatting Rules
-
Hi Hampton,
I got this to work:
Place a workbook_SheetChange event in the workbook code sheet.
Code
Display MorePrivate Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range) Dim iNts As Range Set iNts = Intersect(Sheet1.Range(target.Address), Sheet1.Range("C7:U41")) If iNts Is Nothing Then Set iNts = Intersect(Sheet2.Range(target.Address), Sheet2.Range("C7:U41")) If iNts Is Nothing Then Set iNts = Intersect(Sheet3.Range(target.Address), Sheet3.Range("C7:U41")) If iNts Is Nothing Then Set iNts = Intersect(Sheet4.Range(target.Address), Sheet4.Range("C7:U41")) If Not iNts Is Nothing Then Application.ScreenUpdating = False Call background_color_count Application.ScreenUpdating = True End If End Sub
Then the sub in a module.
Code
Display MoreSub background_color_count() Dim x As Long, c As Range x = 0 For Each c In Sheet1.Range("C7:U41") If c.DisplayFormat.Interior.Color <> 16777164 And c.DisplayFormat.Interior.Color <> 16777215 Then x = x + 1 Next c For Each c In Sheet2.Range("C7:U41") If c.DisplayFormat.Interior.Color <> 16777164 And c.DisplayFormat.Interior.Color <> 16777215 Then x = x + 1 Next c For Each c In Sheet3.Range("C7:U41") If c.DisplayFormat.Interior.Color <> 16777164 And c.DisplayFormat.Interior.Color <> 16777215 Then x = x + 1 Next c For Each c In Sheet4.Range("C7:U41") If c.DisplayFormat.Interior.Color <> 16777164 And c.DisplayFormat.Interior.Color <> 16777215 Then x = x + 1 Next c Sheet4.Range("AD43").Value2 = x End Sub
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:
-
-
You can replace the code in the workbook_SheetChange event with:
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!