Sun 01 Dec 2019

Tips & Tricks 173 - Color Code for Official Color of Excel

By |Sunday, December 01st, 2019|Categories: Tips and Tricks|Tags: , , , , , , , |0 Comments

Everybody sees the dark green color of Excel.

The color code for this color is (more…)

Sat 23 Feb 2019

Tips & Tricks 168 - Sum Cells for a Particular Color

By |Saturday, February 23rd, 2019|Categories: Tips and Tricks, VBA|Tags: , , , , , , , |1 Comment

This can be accomplished with VBA only. There is no native formula within Excel to support this. This is a fairly small piece of code..

1. Make a backup of your workbook.
2. Open your workbook and ALT+F11
3. Locate your Workbook name in Project Explorer Window
4. Right click on your workbook name > Insert > Module
5. Copy and Paste the below code in the module
6. Save your file as .xlsm

=SumColorCells(A1:C6,255,255,0)

Next 3 digits are RGB codes. For Yellow, they are 255, 255 and 0...To know the RGB code of any colour, select your cell and click on down pointing arrow on Fill Colour symbol and go to custom to pick up RGB code.

---- Macro by E for Excel ----

```Function SumColorCells(Rng As Range, R, G, B)
Dim Cell As Range
Application.Volatile
For Each Cell In Rng
If IsNumeric(Cell) And Cell.Interior.Color = RGB(R, G, B) Then
SumColorCells = SumColorCells + Cell
End If
Next Cell
End Function```

Sat 28 Nov 2015

Tips & Tricks 120 - Filter by Color Drop Down Menu Greyed Out Problem

By |Saturday, November 28th, 2015|Categories: Tips and Tricks|Tags: , , , , , |6 Comments

Sometimes, you find that though your are having colored cells but Filter by Color is greyed out i.e. it can not be selected.

Following are the possible causes -

1. Your sheets may be grouped. If they are grouped, you will notice word "Group" in the title bar of your Excel workbook.

Another way to check whether the sheets are grouped or not is to right click on any tab name and option "Ungroup Sheets" will appear:

2. There may be blank lines before a color is encountered in your range. Filter doesn't like blank cells. Hence, be watchful.

3. The workbook may be shared. If this is a shared workbooks, the word "Shared" will appear in the title bar of your workbook.

To remove sharing > Review tab > Share Workbook > Uncheck the box in Red zone

4. Also if first colored cell appears after approximately 10000 rows, Excel may not detect the color. To overcome this behavior, put a color in the first cell and now it will enable the Filter by Color. Only thing is that this colored cell will be extra in your filtered list.

5. Edit on 11-Oct-19 - If Filter by color doesn't appear and the worksheet is compatible (>=2007), you play with the column and do some random coloring, or real - at some point it starts showing the menu item.

Credit - Raymond Hilary