Highlight cells on column B based on the content of cells in Column E

[ShirinB]

Hi,

I was a Macro that help me to highlight parallel cells in column B IF cells in column E containing #N/A.
So if a cell in column E row 3 is #N/A I want column B row 3 gets highlighted.


Regards,
Shirin

 

[MarcelBeug]

You can apply conditional formatting to column B with formula =ISNA($E1)

 

[ShirinB]

You can apply conditional formatting to column B with formula =ISNA($E1)

Hi,

Could please let me know which Conditional formatting I need to use?
I picked a "New rule" and then "Use a formula to determine which cells to format" However, it does not highlight anything in Column B.


Regards,
Shirin

 

[JLGWhiz]

If you have a hard time with conditional formatting, Try this

Sub t()
Dim sh As Worksheet, rng As Range
Set sh = ActiveSheet
Set rng = Intersect(sh.UsedRange, Range("B:B"))
sh.UsedRange.AutoFilter 5, "=#N/A"
rng.SpecialCells(xlCellTypeVisible).Interior.ColorIndex = 6
sh.AutoFilterMode = False
End Sub

 

[Trebor76]

Here's another macro (though as it involves looping isn't as good as JLGWhiz's propsed solution):

Option Explicit
Sub Macro1()
    
    Dim lngLastRow As Long, lngMyRow As Long
   
    Application.ScreenUpdating = False
    
    lngLastRow = Cells(Rows.Count, "E").End(xlUp).Row
    
    For lngMyRow = 1 To lngLastRow
        If IsError(Range("E" & lngMyRow)) = True Then
            If Range("E" & lngMyRow).Value = CVErr(xlErrNA) Then 'ONLY '#N/A' errors will be flagged. Change to suit if necessary.
                Range("B" & lngMyRow).Interior.Color = RGB(255, 0, 0)
            End If
        End If
    Next lngMyRow
    
    Application.ScreenUpdating = True
    
    MsgBox "All rows in Col. B where there is a #N/A error in Col. E have been filled red.", vbInformation
    
End Sub

Regards,

Robert

 

[ShirinB]

If you have a hard time with conditional formatting, Try this

Sub t()
Dim sh As Worksheet, rng As Range
Set sh = ActiveSheet
Set rng = Intersect(sh.UsedRange, Range("B:B"))
sh.UsedRange.AutoFilter 5, "=#N/A"
rng.SpecialCells(xlCellTypeVisible).Interior.ColorIndex = 6
sh.AutoFilterMode = False
End Sub

Hi,

This worked fine.

Thank you very much.

Regards,
Shirin

 

[MarcelBeug]

Hi,

Could please let me know which Conditional formatting I need to use?
I picked a "New rule" and then "Use a formula to determine which cells to format" However, it does not highlight anything in Column B.


Regards,
Shirin

Apologies. Definining the formula is one step, chosing "Format" and pick your choices would be the 2nd step.

 

[JLGWhiz]

Hi,

This worked fine.

Thank you very much.

Regards,
Shirin

You're welcome,
Regards, JLG