colourANDtext

[murthigvs]

dear all,

I have a spreadsheet where i would like to 'count' the number of cells with a particular colour AND SPECIFIC text (not 'any text').
I attach a xls with the initials of the person in column A, their colour as seen in cells in next four columns with the particular text in each cell. Therefore, for 'ABC1', in the given range, I would like to count how many cells have been allocated with the particular colour (brown, sorry do not know colour index) and the particular text (1 or 2 or 4 or 5) in the range A1:CK39. Haven't supplied the actual sheet for confidentiality reasons.

[:) Sixthsense :)]

Your sample file don't have mingling text with mingling colors.

Because each color has been allocated for four cells and each text is also formatted to six colors.

[lilsnoop]

Not sure if I understand exactly what you are trying to accomplish, but see the attached workbook and hopefully it will at least get you started. Best of luck!

[lilsnoop]

murthigvs,

See if this workbook will accomplish what you are trying to do. It summarizes colored cells in multiple ways; i.e. counts the number of cells a certain color, sums the number in a certain color for any value listed in the same cell, and it will now distinguish and count cells with the same background and number in it like you wanted. The formulas will have to be amended for your actual workbook ranges, etc. I had a roadblock with one of the formulas, but thanks to "xladept" on this forum he was able to help assist you as well in this project.

Hope it works for you!

[murthigvs]

thanks for solving this. sorry for delayed reply.

[murthigvs]

dear lilsnoop,

the formula for the count of 'color and text' does not seem to be working. pl check the attached sheet - E12 should read '2' as there are 2 cells (like B12) with 'brown' color and text '1' within the range B2:E2 as specified in the formula, but is reading '1' which is wrong. can u pl help?

[lilsnoop]

Hello murthigvs,

Well I guess I hadn't caught that! I found an alternate method, but your color choices were a little difficult to find the codes for so I changed them to something a little simpler for testing purposes; nevertheless, I believe I accomplished what you want. The new vba coding for counting the numbers by color is in module 3 and you'll have to look at the formulas in the cells to see how they work. See attached workbook.

[lilsnoop]

Did this work for you?

Edited to add:
If you want to use your colors, you'll want to use this code to find the color code number, which you can than use to edit or modify the vba code in module 3. Just insert this code in a module and attach to a button. You'll want to select a cell which has a background color and then click to activate this macro and then a pop-up window will appear with the color code # for that specific background color.

Sub check()
MsgBox Selection.Interior.ColorIndex
End Sub