find range of non-blank cells in colum

[Guest]

Hello~
I need assistance in construction a macro which will do the following:
- find a range of cells in colum A with data
- use that range of rows to search Columns B for any blank cells
- if found, highlight or fill the blank cells with Red.
- then repeat the procedure on collumn D, AG, AE etc..

Thanks in advance.
--Don

 

[Guest]

Why not use conditional formatting. Highlight Cells B2:B??? and select Format
-> Conditional Formatting. Change the drop down from Cell Value to Formula
and add this to the formula text box

=AND(B2="", A2<>"")

Then just select the type of formatting you want and you are good to go...

 

[Guest]

Thanks Jim~
That will work in some situations, but in this case I frequently, 4 or more
times per week receive spreadsheets with 167 columns and would have to set up
conditional formatting every time for those sheets. A lot of extra work...

 

[Guest]

Here is some code. Since it sounds like you will be doing a lot with it I
have tweaked it for efficiency... You need to specify the column to search
and how many columns to ofset to the column you want to compare to. This
checks column 1 (A) against offset 1 (column B)...

Sub test()
Call ColourStuff(Columns(1), 1)
End Sub

Sub ColourStuff(rngInput As Range, byVal ColumnOffset as Integer)
Dim rngFormulas As Range
Dim rngConstants As Range
Dim rngAll As Range
Dim rng As Range

On Error Resume Next
Set rngFormulas = rngInput.SpecialCells(xlCellTypeFormulas)
Set rngConstants = rngInput.SpecialCells(xlCellTypeConstants)
On Error GoTo 0

If Not rngFormulas Is Nothing And Not rngConstants Is Nothing Then
Set rngAll = Union(rngFormulas, rngConstants)
ElseIf Not rngFormulas Is Nothing Then
Set rngAll = rngFormulas
ElseIf Not rngConstants Is Nothing Then
Set rngAll = rngConstants
End If

If Not rngAll Is Nothing Then
For Each rng In rngAll
If rng.Value <> "" And rng.Offset(0, ColumnOffset ).Value = ""
Then
rng.Offset(0, ColumnOffset ).Interior.ColorIndex = 5
End If
Next rng
End If
End Sub