Poll

Aboot this blog

This blog is a cross between a scrapbook and a diary. I hope you find something here of interest. If you’d like to keep up with things as I see them then you can subscribe to my news feed by clicking the icon below:

feed-icon-28x28.png

DISCLAIMER

Please note that any reviews/recommendations are based solely on my own experience and does not constitute a guarantee that you will have the same experience. Please do your own research before parting with any money - the risk is all yours!

The Briticiser

Looking for my world famous US to GB English spelling converter?

THE “BRITICIZSER”

www.us2uk.eu

IT’S HERE

Tags

  Odds and Sods     Software     Equipment     Tutorials     Plausible Lies     Windows     Browsers     PHP         Reviews     Utilities     Family     Overcoming Disabilities     Education     VBA     Bits and Bobs     Bookmarks     Acrobat     Hardware     News     Excel     Word     Flatpress  

Find me on Facebook

facebook-icon-28x28.pngVisit my Briticiser Facebook Page to keep up with things here - lots of reviews of stuff I’ve really used and also all kinds of helpful computer tips with a heavy bias towards SAVING YOU TIME.

Tip of the Day : How to test for background colours in Excel

Permalink: http://paul.us2uk.eu/?x=entry:entry130430-173338

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?

Here’s a neat and not too difficult to follow way of doing that. These instructions apply to all versions of Excel up to XP/2003 but should be easily adaptable for use in later versions.

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).

Sub testbgcolor()
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”)
    End With
Next I
End Sub

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.

Sub testbgcolor()
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”)
    End With
Next I
End Sub

Have fun!

Ching! Ching!

If you found anything on this site of use, interesting, or even mildly amusing please consider tipping a few pennies in the jar to help look after our son, Georgi, to whom this blog is shamelessly dedicated.

Georgi contracted encephalitis and fell into a coma in the summer of 2007. He’s a strong boy and survived, but he suffered brain damage and still can’t walk or talk (well, not very well - but he does try). He’s growing all the time and every penny we collect goes towards his upkeep, and towards his future care requirements.

Thank you.