How do I set a range and remove cell fill color for any cell populated within it?.

I have a range of cells with a fill color.  I need to remove the fill color for any cell within that range that is populated.  How do I get started?
Jay WilliamsOwnerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
Is it a continuous range?

An example workbook would help
0
Rory ArchibaldCommented:
Populated with formulas, or values, or a mixture of the two?
0
Jay WilliamsOwnerAuthor Commented:
Thanks.  Here's the attachment. That would be for any character or number.
Range.xlsx
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Glenn RayExcel VBA DeveloperCommented:
Create a Conditional Formatting rule and apply it to your range.

The rule would be:
=A1<>""
and you would select "No Color" for the Fill property of the cells.
EE-CF-nofill.png
Regards,
-Glenn
0
Rory ArchibaldCommented:
In code, you could use:

On Error Resume Next
range("A1:Ab21").SpecialCells(xlCellTypeConstants).Interior.ColorIndex = xlcolorindexnone
On Error Goto 0

Open in new window


or manually use f5, special..., Constants to select all of them and then you can clear the fill colour.
0
Glenn RayExcel VBA DeveloperCommented:
Follow-up to my previous post:

To apply this rule:
1) Highlight the entire range that is applicable (A1:AB21 in your example).
2) From Excel menu, select Home - Conditional Formatting - New Rule...
3) Select "Use a formula to determine which cells to format
4) In the box labeled "Format values where this formula is true:" enter
=A1<>""
5) Click the "Format" button
6) Select the "Fill" tab, then click "No Color" on top.  Click the "OK" button
7) Click the "OK" button.

Type anything in your shaded area and note the results.  The grey should go away in any cell with a value.

-Glenn
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Roy CoxGroup Finance ManagerCommented:
Try this

On Error Resume Next
With ActiveSheet.Range("A1").CurrentRegion.SpecialCells(xlCellTypeConstants).Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
On Error GoTo 0

Open in new window

0
Glenn RayExcel VBA DeveloperCommented:
I note that you have a large, merged area from G1:AB3.  The entire area will be affected (i.e., unfilled) if any value is entered there.
0
Jay WilliamsOwnerAuthor Commented:
Ok, we're on the right track, but I didn't give you the whole story (surprise!).  Sorry.  I really want the fill to toggle "if" the cell is or is not null.  I couldn't get the rule to work at all; Rory's code worked, but I had to run it manually.  Ideally, the fill automatically changes when the value is added or deleted.
0
Rory ArchibaldCommented:
Right-click the worksheet tab, choose View code, and paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1:AB21")) Is Nothing Then
        On Error Resume Next
        With Range("A1:Ab21")
            .SpecialCells(xlCellTypeConstants).Interior.ColorIndex = xlColorIndexNone
            .SpecialCells(xlCellTypeBlanks).Interior.Color = 8421504
        End With
    End If
End Sub

Open in new window

then save the file as a macro enabled workbook.
0
Rob HensonFinance AnalystCommented:
There are a number of merged areas in that big grey range.

G1:AB3
A20:O21
Q19:AB21

How is each range to be handled?

For the range to be formatted at a change in cell (blank to non-blank and vice verse or any value entered) you can normally use Conditional Formatting with no need for VBA routines.

Thanks
Rob H
0
Jay WilliamsOwnerAuthor Commented:
The merged cell issue really isn't one, I just gave you a poor example.  For my purposes now, there will be no merged cells inside the range.

I couldn't make Rory's second code with the if statements run at all, not even by stepping in manually.  There must be something fundamental about running Excel code that I'm not getting--I'm more an Access guy.  I did save the file in xlsm format; I thought maybe I needed to create a rule that ran the code when the value is entered/deleted, but the macro name does not appear.  Do I need to make it public and put it in a module--or where?

I do want the fill of each cell inside the range to automatically change with the cell value;  null = fill, not null = no fill.
0
Rob HensonFinance AnalystCommented:
If there are no merged cells then follow the steps above for applying the conditional format.

Sample file attached.
Range.xlsx
0
Rory ArchibaldCommented:
If you put it in the right place, the code runs automatically as you enter data/clear data from the cells in the sheet. The code must be in the worksheet code module, as I indicated.
0
Rob HensonFinance AnalystCommented:
Conditional formatting doesn't need any VBA or particular file format; only thing that does affect it is the calculation mode; needs to be Automatic or formatting will not change until calculation is forced with F9 press.
0
Glenn RayExcel VBA DeveloperCommented:
Nothing wrong with code, but you don't need it.  Conditional formatting should work if you followed the steps I described in my follow-up post.  Rob Henson's example demonstrates it perfectly.
0
Rob HensonFinance AnalystCommented:
Hi Glenn, I realised I had omitted from my comment above credit to you for the steps to apply the CF but Rory had already commented so I couldn't amend. Apologies!!
0
Glenn RayExcel VBA DeveloperCommented:
No problem.  Thanks for posting an example; I almost did, but hoped the steps listed would work.
0
Jay WilliamsOwnerAuthor Commented:
You were quite right, Glenn, I didn't need the code.  I must not have followed your instructions properly. It's exactly what I was after.  Thanks. all.
0
Glenn RayExcel VBA DeveloperCommented:
Glad I could help.  I love VBA, but I do prefer solutions that don't use it.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.