asked on

Reset Colors in Boxes

I've been trying to reset the colors in all boxes (RangeName = "Boxcolors") to yellow by writing a Macro. Unfortunately, my VBA skills are not that good. If you're up for it, take a look at the Macro for resetting the boxes to yellow. It's a small macro that requires a tweek.

Much thanks in advance,

B.
C--Data-X-Data-Trash-Clear-Map.xlsm

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Roy Cox

Try this

Sub ClearWorksheet()
   
    With Range("BoxColors").Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
   
End Sub

Open in new window

To change to yellow

    Range("BoxColors").Interior.colorindex=vbYellow

Open in new window

Rgonzo1971

or simply

Sub ClearWorksheet()
    Range("BoxColors").Interior.Color = vbYellow
End Sub

Open in new window

Bright01

ASKER

Martin,

As always..... "THANK YOU". I worked on that for 45 min. I've got to take a VBA class at some point.

Thanks,

B.

Martin Liss

You're welcome and I'm glad I was able to help.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written (including three new ones) that may interest you.

Marty - Microsoft MVP 2009 to 2016
Experts Exchange MVE 2015
Experts Exchange Top Expert Visual Basic Classic 2012 to 2016

Roy Cox

You don't need to Loop, you have a Named Range as myself and rGonzo used in our suggestions. The difference won't be great unless you have lots of cells, but avoiding Loops whenever possible is good practice.

Martin Liss

I agree and if someone wants to request attention so that points can be split, I have no objection.

Roy Cox

It's not a problem to me, just thought I would make the observation.

Bright01

ASKER

Guys.... thanks for the outstanding comments.