Cormat blank cells

[brian5857]

is there code that will turn all of the empty or unused cells in a worksheet a color??

 

[nbrcrunch]

you can use conditional formatting and apply it to the cells you want. Are you sure you want all 16+ million cells filled. Your book will become huge when you try to save it.

 

[babycody]

Select cell A1. Then go to the menu bar and choose Format>Conditional Formatting. From the pull down menu choose Formula is then in the next space enter =A1="" . Now click on the Format button and choose the patterns tab. Choose a background color you like. Press OK til you are out of this.

Now while A1 is still selected right click and choose copy. While holding the Ctrl key down hit the space bar. Right click on the selected area. Choose Paste Special. Then choose formats.

If you don't still have column A selected hit the Ctrl+space bar again. Right click and choose copy. Now press these keys at the same time in this order Ctrl+space bar+Shift+right arrow. Now right click and choose Paste Special. Then choose formats.

It takes more time to read this than it takes to do it.

 

[babycody]

you can use conditional formatting and apply it to the cells you want. Are you sure you want all 16+ million cells filled. Your book will become huge when you try to save it.

I thought it would bloat the file too, but I used a blank workbook and and it was 14KB when I saved it. This was for one sheet only.

 

[Yogi Anand]

Hi babycody:

Quoting from the method that you described ...

"Select cell A1. Then go to the menu bar and choose Format>Conditional Formatting. From the pull down menu choose Formula is then in the next space enter =A1="" . Now click on the Format button and choose the patterns tab. Choose a background color you like. Press OK til you are out of this."

The above is all that is required ... no further copying is necessary.

What do you think?

 

[babycody]

Hi babycody:

Quoting from the method that you described ...

"Select cell A1. Then go to the menu bar and choose Format>Conditional Formatting. From the pull down menu choose Formula is then in the next space enter =A1="" . Now click on the Format button and choose the patterns tab. Choose a background color you like. Press OK til you are out of this."

The above is all that is required ... no further copying is necessary.

What do you think?

Hey Yogi

If I just followed what you quoted me on I would end up with cell A1 formatted only. My assumption was that OP wants to have entire sheet conditional formatted. Somewhere along the way I have been taught to copy conditional formats down and then over. Being the MVP that you are I wonder if I am missing your point, or that you know something I haven't yet discovered. If so please let me in on yet another Excel lesson.

 

[Yogi Anand]

Hi babycody:

With all the cells selected, and cell A1 as the ActiveCell, using ...

Formula Is ... =A1=""

Conditional Formatting will apply to all the cells of the worksheet ... without any further copying. However, to convince yourself I suggest you try that and then let us take it from there!

 

[babycody]

Absolutely right Yogi! Thanks for that. I found where I was told to do this. http://www.mrexcel.com/board2/viewtopic.php?t=175239&highlight=conditional
I guess it was because the A was absolute. Not possitive though. This just stuck in my head because I remember trying it the other way, and it didn't work. I have assumed ever since that this was the way to do it.

 

[Yogi Anand]

Good Job babycody for sorting it all out to your satisfaction ... now let us keep EXCELing!