Conditional Formatting using VBA - non macro

[jharih1]

My conditional formatting code is not seeming to be working. I have two columns - one of which has a vlookup. What I wish to do is do conditional formatting on the column without the vlookup. So if the vlookup returns an #N/A, the conditional formatting should highlight the cell Red, and put up a msgbox.

Here is the code I am working with - and I dont think it is right. I do not wish to do a macro, and would prefer to have this code in the worksheet sub function.

Private Sub Worksheet_Change(ByVal Target As Range)


'Checks if vlookup function returns an N/A.
'If it is blank or has valid data, no conditional formatting should be used.


Dim i As Long
For i = 1 To ActiveSheet.UsedRange.Rows.Count
    If Range("K" & i).Value = "N/A" Then
        Range("J" & i).Font.Color = vbRed
        MsgBox "Please Enter Valid Vendor Information."
    End If
Next i


End Sub

Please help!

 

[Jerry Sullivan]

Hi jharih1,

The existing code isn't doing what you want because you have "N/A" instead of "#N/A". Since the code will error if you try to compare the .Value property to an error, use the .Text property instead...

    If Range("K" & i).Text = "#N/A" Then

There's a few drawbacks the approach you're taking.
1. Each time any cell on the sheet is changed by the user, this process steps through all the cells in Col K of the usedrange instead of just the one(s) that had a change in Col J.

2. If a vendor is removed from your lookup table on another sheet, the worksheet_change event on this sheet won't be triggered. The cell in Col K will show #N/A, with no highlighting in J or error message.

3. If the user changes multiple cells in Col J (e.g Pasting a range of values), the message box will pop up multiple times (without identifying which cell has the error).

Can you share with us why you want to use VBA instead of Excel's worksheet Conditional Formatting (CF)?
For the application you describe, I'd suggest using an IFERROR function with the VLOOKUP that includes your error message.
=IFERROR(VLOOKUP(J5,MyVendorData!A:B,2,0), "Please Enter Valid Vendor Information.")

You can use then Worksheet CF in Column J to test for that error message:
=$K2="Please Enter Valid Vendor Information."

 

[jharih1]

The main reason for using VBA was because of the usage of the msgbox, because users are not really caring about the errors (We have checks in place right now), and trying to find workarounds...so using the VBA code we feels eliminates some of the user errors so that our application is reading correct data from this table.. However, that being said - I modified my code and got it to work. At least this does what we need it to do.

Thanks again for your response

Private Sub Worksheet_Change(ByVal Target As Range)


'Checks if vlookup function returns an N/A.
'If it is blank or has valid data, no conditional formatting should be used.


Dim i As Long
For i = 2 To 9999
    If Cells(i, 10).Value = "ERROR" Then
       Cells(i, 10).Interior.Color = RGB(198, 30, 2)
       Cells(i, 9).Interior.Color = RGB(198, 30, 2)
       MsgBox "Please enter valid vendor information"
    Else:
       Cells(i, 10).Interior.ColorIndex = 0
       Cells(i, 9).Interior.ColorIndex = 0
    End If
Next i
End Sub