Filtering an Excel spreadsheet to show just the rows you want is relatively easy to do, simply by using the AutoFilter function. But what if you find yourself in a situation where you want to filter your results to only show rows where a cell has a particular background colour?
The way to achieve this involves using a little bit of VBA code (actually this bit here) but I’ll explain what it does and how you can adapt it for your own requirements. The VBA code just needs to be copied and pasted as a new module in the Visual Basic Editor, which you can open simply by pressing Alt+F11 while working in Excel.
Begin by making a note of which column contains the cell with the background colour you want to test for, and then pick an empty column where you want the macro to put a “Yes” (if the colour matches what you’re looking for). You will then be able to use the AutoFilter function to just show rows where there is a “Yes” in that column.
The macro will run all the way down your spreadsheet and test each row in the column where you have the colours you want to test for, and write a “Yes” in the corresponding cell in the empty column you chose.
In order for this to work you need to enter a colour index value in your macro. This is the number of the colour which can be found in THIS LIST. For instance Green is 4, Yellow is 6 and Cyan is 9, and so on.
In its basic form the following piece of code will test for cells with a yellow background in column A, starting at row 1 and putting “Yes” or “No” in the adjacent cell (e.g. if cell A1 is coloured yellow then the macro will write “Yes” in cell B1).
Dim LR As Long, I As Long
LR = Range(”A” & Rows.Count).End(xlUp).Row
For I = 1 To LR
With Range(”B” & I)
.Value = IIf(.Offset(, -1).Interior.ColorIndex = 6, “Yes”, “No”)
If this matches exactly what you want to achieve then great, just open up the VB Editor (Alt+F11) and choose “Insert…Module” from the menu then copy and paste the above code in, close the editor (no need to save anything) and then go to “Tools…Macros…” in your Excel menu and you’ll see your new macro there. Just run it and you’re done.
But suppose you want to test for another colour, and your coloured cells are (say) in column C, and your only “spare” column into which you want the macro to write “Yes” or “No” is column L, and your list doesn’t start in the first row - say it starts in row 35?
Well, for the exact scenario above your code would look something like this. In this example we’re testing for colour number 38, which is a kind of pinky colour.
Note the bits of code which are coloured red. You can marry these up to the values given in the scenario outlined above - all except one. See that line in the middle with .Value = IIf(.Offset(, -9)? That’s the number of columns between column C and column L. In our original example this was just “-1” (as we were just using columns next to each other). If, for example, you wanted to test for a colour in column D and get the macro to write “Yes” in column G then this value would be -3.
Dim LR As Long, I As Long
LR = Range(”C” & Rows.Count).End(xlUp).Row
For I = 35 To LR
With Range(”L” & I)
.Value = IIf(.Offset(, -9).Interior.ColorIndex = 38, “Yes”, “No”)