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

Is it a continuous range?

An example workbook would help

Populated with formulas, or values, or a mixture of the two?

Thanks.  Here's the attachment. That would be for any character or number.
Range.xlsx

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.

Regards,
-Glenn

In code, you could use:

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

Select allOpen in new window

or manually use f5, special..., Constants to select all of them and then you can clear the fill colour.

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

Select allOpen in new window

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.

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.

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

Select allOpen in new window

then save the file as a macro enabled workbook.

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

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.

If there are no merged cells then follow the steps above for applying the conditional format.

Sample file attached.
Range.xlsx

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.

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.

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.

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!!

No problem.  Thanks for posting an example; I almost did, but hoped the steps listed would work.

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.

Glad I could help.  I love VBA, but I do prefer solutions that don't use it.