Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Create named range based on cell fill

Posted on 2011-10-26
Medium Priority
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
  • 4
  • 2
LVL 24

Expert Comment

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

Expert Comment

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

Accepted Solution

StephenJR earned 2000 total points
ID: 37031833
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 37031998
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

ID: 37032007
Concise coding! Great, thank you very much!
LVL 24

Expert Comment

ID: 37032011
My pleasure.

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

581 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