Highlight blank cells
 

I have multiple Excel files. Each one of them has a different number of
records in them. Some have alot some have very few.

I need to write in VB to highlight any cell that is blank. Could anyone
help me out?

Highlight blank cells
 

You can do this directly:

Sub ColorBlanks()
Dim Rng As Range
For Each Rng In Selection
With Rng
If .Value = vbNullString Then
.Interior.ColorIndex = 3 'red
Else
.Interior.ColorIndex = xlColorIndexAutomatic
End If
End With
Next Rng
End Sub

Select the cells to test then run the macro. As an alternative, you could
use Condition Formatting (CF). Select the cells to test, open the CF dialog
from the Format menu, change "Cell Value Is" to "Formula Is" and enter

=A1=""

Change A1 to the first selected cell and choose your formatting.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"pokdbz" wrote in message
...
I have multiple Excel files. Each one of them has a different number of
records in them. Some have alot some have very few.

I need to write in VB to highlight any cell that is blank. Could anyone
help me out?

Highlight blank cells
 

Selection.SpecialCells(xlCellTypeBlanks).Select

"pokdbz" skrev:

I have multiple Excel files. Each one of them has a different number of
records in them. Some have alot some have very few.

I need to write in VB to highlight any cell that is blank. Could anyone
help me out?

Highlight blank cells
 

Great
Is there a way to figure out what the range will be on its own without me
selecting it?

Row 1 will always have headings in it.
So is it possible to find out what the last column is an use that for the
selection criteria?

Also Column A will never be blank so can you use this info to automatically
select the range? so that it doesn't color the outside cells?

"Chip Pearson" wrote:

You can do this directly:

Sub ColorBlanks()
Dim Rng As Range
For Each Rng In Selection
With Rng
If .Value = vbNullString Then
.Interior.ColorIndex = 3 'red
Else
.Interior.ColorIndex = xlColorIndexAutomatic
End If
End With
Next Rng
End Sub

Select the cells to test then run the macro. As an alternative, you could
use Condition Formatting (CF). Select the cells to test, open the CF dialog
from the Format menu, change "Cell Value Is" to "Formula Is" and enter

=A1=""

Change A1 to the first selected cell and choose your formatting.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"pokdbz" wrote in message
...
I have multiple Excel files. Each one of them has a different number of
records in them. Some have alot some have very few.

I need to write in VB to highlight any cell that is blank. Could anyone
help me out?

Highlight blank cells
 

Would it be possible to incorporate the ctrl-shift-right and ctrl-shift-down
to select the edge of the regions?

"Chip Pearson" wrote:

You can do this directly:

Sub ColorBlanks()
Dim Rng As Range
For Each Rng In Selection
With Rng
If .Value = vbNullString Then
.Interior.ColorIndex = 3 'red
Else
.Interior.ColorIndex = xlColorIndexAutomatic
End If
End With
Next Rng
End Sub

Select the cells to test then run the macro. As an alternative, you could
use Condition Formatting (CF). Select the cells to test, open the CF dialog
from the Format menu, change "Cell Value Is" to "Formula Is" and enter

=A1=""

Change A1 to the first selected cell and choose your formatting.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"pokdbz" wrote in message
...
I have multiple Excel files. Each one of them has a different number of
records in them. Some have alot some have very few.

I need to write in VB to highlight any cell that is blank. Could anyone
help me out?