Create named range based on cell fill

Posted on 2011-10-26
Last Modified: 2012-06-27
Dear Experts:

I would like to create a named range on the worksheet "DATA" of the current workbook based on cell fill color.

The macro is to search for the "first" (C15) and "last" (G32) occurrence of red filled cells (RGB 230, 184, 183) …
… and create a workbook-level scope with that range (C15:G32)

I have attached a sample file for your convenience.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas
Question by:AndreasHermle
    LVL 24

    Expert Comment

    Andreas - have you got the wrong colour? That gives me a grey, not a red.
    LVL 24

    Expert Comment

    Now wondering if this is to do with me.
    LVL 24

    Accepted Solution

    I found this worked for me if I changed the RGB values:
    Sub x()
    Dim rFind As Range, sAddr As String, r As Range
    With Application.FindFormat
        .Interior.Color = RGB(255, 128, 128)
    End With
    With Sheet1.Cells
        Set rFind = .Find(What:="*", LookAt:=xlWhole, SearchFormat:=True)
        If Not rFind Is Nothing Then
            sAddr = rFind.Address
                Set r = rFind
                Set rFind = .Find(What:="*", After:=rFind, LookAt:=xlWhole, SearchFormat:=True)
            Loop While rFind.Address <> sAddr
            Set r = Range(r, rFind)
            r.Name = "Myrange"
        End If
    End With
    End Sub

    Open in new window


    Author Comment

    Dear Stephen,

    the macro works great (even with my color fill).

    thank you very much for your swift and professional support.

    regards, Andreas

    Author Closing Comment

    Concise coding! Great, thank you very much!
    LVL 24

    Expert Comment

    My pleasure.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Introduction This Article is a follow-up to my Mappit! Addin Article (, it was inspired by an email posting I made to EUSPRIG (, I will briefly cover: 1) An overvie…
    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now