Using VBA to color selected cells if empty?

[diannakha]

Hello, I'm fairly new to using macros and I need some assistance. I'm not interested in using conditional formatting for this task, but if all else fails I will have to do so.

What I'd like to see happen is once I run a macro, the multiple cells I have selected (either by a predetermined range or by highlighting said cells) will be colored orange if they are empty. The range will probably be B5 to W10, though I'd like it a lot more if it was the cells I had highlighted. I tried using the following code, but of course it only works for one cell.

 Sub Highlight_Blanks()
'
' Fill any cells which are blank
'
If ActiveCell.Value = "" Then ActiveCell.Interior.Color = 49407
End Sub

Thanks so much for any help you can give me.

 

[njimack]

Welcome to the board.

Sub Highligh_Blanks()
Selection.Interior.ColorIndex = xlNone
Selection.SpecialCells(xlCellTypeBlanks).ColorIndex = 46
End Sub

 

[diannakha]

Thanks for the welcome and the reply, Neil! I tried that and I got this error:

Run-time error '438':
Object doesn't support this property or method

If I click the debug button, it highlights this portion of the code:

Selection.SpecialCells(xlCellTypeBlanks).ColorIndex = 46

If it makes a difference, I'm using Excel 2003. Sorry!

 

[VoG]

Try

Selection.SpecialCells(xlCellTypeBlanks).Interior.ColorIndex = 46

 

[diannakha]

It works!! THANK YOU SO MUCH!! You've both helped me out tremendously! There isn't enough exclamation marks in the world to tell you how you've instantly ridded me of my headache.