I'm using Excel 2003. All I need to do is have the cells in the O column count the number of cells I have manually changed to the color of per row. I'll be changing the color to blue in the range of B4 to N33. I'm a self taught Excel user, and this is the first time I've attempted something this complex. Thanks.
Simply count the colored cells per row
-
-
-
Re: Simply count the colored cells per row
You will need to use VBA and create a user defined function.
This article explains how to do it:
http://support.microsoft.com/kb/2815384
You only need to go through steps 1 to 8 unless you want to save the udf as an addin to use on other workbooks.
Here is another one, within OzGrid's own database: http://www.ozgrid.com/VBA/sum-count-cells-by-color.htm
-
Re: Simply count the colored cells per row
This will do what you want. It assumes your Blue is the Excel default blue. AKA "vbBlue".
Code
Display MorePrivate Sub Worksheet_SelectionChange(ByVal Target As Range) Dim i As Integer Dim j As Integer Range("O4:O33").ClearContents For i = 4 To 33 'Rows For j = 2 To 14 'Col If ActiveSheet.Cells(i, j).Interior.Color = vbBlue Then Range("O" & i).Value = Range("O" & i).Value + 1 'Count number of blue cells per row End If Next j Next i End Sub
-
Re: Simply count the colored cells per row
Quote from yegarboy;694309This will do what you want. It assumes your Blue is the Excel default blue. AKA "vbBlue".
Code
Display MorePrivate Sub Worksheet_SelectionChange(ByVal Target As Range) Dim i As Integer Dim j As Integer Range("O4:O33").ClearContents For i = 4 To 33 'Rows For j = 2 To 14 'Col If ActiveSheet.Cells(i, j).Interior.Color = vbBlue Then Range("O" & i).Value = Range("O" & i).Value + 1 'Count number of blue cells per row End If Next j Next i End Sub
Ok so after I create a module in VBE, how do I get it to work? Do I need to use it as a function?
-
Re: Simply count the colored cells per row
It needs to be in a Worksheet Module. In VBE you need to click on the actual sheet where want the behaviour and put the code there. It will run when the Worksheet Selection changes. It is an event handler routine... Google it.
-
-
Re: Simply count the colored cells per row
That was it!!!! Thank you guys so much for helping me with this. It works perfectly just how I dreamed it would, and this is going to save me from adding each row manually, and make my life so much easier. Amazing.
-
Re: Simply count the colored cells per row
Glad to be of service
Pay it forward.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!