looping to find empty cells and color the offset (0, 1) cell.

[Neil35]

Hello, Ive been trying to loop through my sheet of loans and identify the loans that do not have loan numbers. If there is not a loan number in the active cell and the activecell.offset (0, 1) is one of my 6 different banks, then i want to color the the cell red. if there is a loan number in the active cell, i want it to go to go on with the search. ive tried several different techniques and the VB is not regognizing the empty cell. below is one that i have tried.

Do While ActiveCell.Value = "Neils Report"
If ActiveCell.Offset(0, 0).Value = "" Then
        If ActiveCell.Offset(0, 1).Value = "US Bank" or "Wells Fargo" or "Chase" Then
                ActiveCell.Offset(0, 1).Interior.ColorIndex = 22
            Else
                ActiveCell.Offset(1, 0).Select
            End If '
            End If

[Richard Buttrey]

Hi, and welcome to the forum.

You'll need to upload the workbook and clearly explain by reference to specific cells what you expect to happen and why. You may not need a macro if there is a conditional formatting solution.

[Neil35]

Lock Check For Errors USBank EZD.xlsm

I want to search column C and if its blank I want to check column D for the bank, and if its a certain bank, then color the cell red. Not all the banks will require a loan number in column C. Thanks for your help.

[Mumps1]

Which banks would be colored red? Which banks require a loan number?

[Richard Buttrey]

Hi,

Maybe a conditional format entered in C2

=AND(C2=" ",OR(D2="US Bank",D2="Wells Fargo",D2="Chase"))

and applied to the whole of column C.

[Neil35]

there are 20 banks that require loan numbers and 7 that do not require a loan number.

i will give you a few to test unless you will need all of them.

Do need a loan number in [C]

Chase
US Bank
SunTrust
Wells Fargo
VHDA
BB&T

[apo]

Hi..

Try this..

Private Sub CommandButton1_Click()
    Dim fArr
    Application.ScreenUpdating = False
    fArr = Application.Transpose(Range("P3:P8").Value) ' Adjust range to suit
    With Range("A1").CurrentRegion
        .AutoFilter 3, " "
        .AutoFilter 4, fArr, xlFilterValues
        .Columns(4).Offset(1).Resize(.Rows.Count - 1).SpecialCells(12).Interior.ColorIndex = 22
        .AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub

[Neil35]

Thanks for the help guys, I was able to use the conditional formatting formula provided. It seems to be working. This will save me a bunch of time!.