Excel VBA- Conditional formatting

EXCEL VBA USING CONDITIONAL FORMATTING

Suppose we have to color entire row of the data range whenever the Revenue column has #N/A. To do this using VBA, following codes can be used. The customer column is assumed to be in column A and begins at A1 cell.

Customer	Revenue	Cost	Profit/Loss
Bernard	#N/A	179.19	#N/A
Calvert	193.12	181.68	11.44
Carter	137.08	113.68	23.40
Carl	191.85	162.86	28.99
Ansel	173.84	152.35	21.49
Aubrey	187.42	178.26	9.16
Burton	#N/A	172.41	#N/A
Channing	191.36	162.27	29.09
Arnold	#N/A	172.44	#N/A
Alfred	144.18	114.63	29.55
Chatwin	181.89	165.70	16.20
Alton	#N/A	185.64	#N/A
Alston	174.73	161.97	12.77
Amery	113.98	100.41	13.57
Casper	#N/A	129.03	#N/A
Blair	131.32	107.38	23.94
Baird	102.82	91.95	10.87
Bond	190.32	181.14	9.18
Andrew	#N/A	133.72	#N/A
Alfie	181.47	159.72	21.75
Carrick	121.35	94.71	26.64
Buck	131.43	113.32	18.11
Bowen	123.35	106.52	16.83
Bruce	196.36	168.33	28.02
Blake	153.79	142.69	11.11
Ambrose	#N/A	94.71	#N/A
Austin	153.90	131.66	22.23

Sub pCallCF()

    Dim rngValues As Range

    Dim strCellAddr As String

    strCellAddr = Range("B2").Address(RowAbsolute:=False)

    Set rngValues = Range("A2").Resize(Range("A1").CurrentRegion.Rows.Count - 1, 4)

    Call CondiFormat(rngValues, strCellAddr)

End Sub

Sub CondiFormat(rngData As Range, rngCell As String)

    rngData.FormatConditions.Add Type:=xlExpression, Formula1:="=ISNA(" & rngCell & ")"

    rngData.FormatConditions(rngData.FormatConditions.Count).SetFirstPriority

    With rngData.FormatConditions(1).Interior

        .ColorIndex = 8

    End With

    rngData.FormatConditions(1).StopIfTrue = False

End Sub