CODEWITHSUNDEEP
excelvba
nick lanta
nick lanta

Reputation: 638

IF statement to trigger specific conditional formatting?

I have an IF statement that triggers if assessment results have been placed in columns B & C.

Some of the info will be filled out, but sometimes there will be some blank cells. I have conditional formatted triggered if there is a name in the header: 

If Range("C4") <> "" Then
    Call Compliance_Heatmapping
End If

and then a formula to trigger cells in column D if there is a discrepancy between the two results: 

With .Range("D5:D27)
         .FormatConditions.Add Type:=xlExpression, Formula1:="=$B5<>$C5"
etc... etc... to format it
End With

but is there a way to avoid any highlighting whenever there is only data in either columns B or C but not both? Sometimes the assessment results will be inconsistent where a manager will only complete half, but the employee completes it all, and I only want it to apply to when there is data in both columns in that row.

Upvotes: 1

Views: 156

Answers (2)

Nathan_Sav
Nathan_Sav

Reputation: 8531

Your conditional formatting function needs to be like so

AND($B5<>$C5,(not(ISBLANK(B5))+not(ISBLANK(C5))>1))

NOT is logical reversal, so ISBLANK checks the cell if it's blank, so NOT(ISBLANK) checks for population. TRUE=1 FALSE=0, so TRUE and TRUE=2, TRUE and FALSE=1, FALSE and FALSE=0

Upvotes: 1

Error 1004
Error 1004

Reputation: 8220

Try:

If .Range("C4") <> "" Then
    If .Range("B5").value <> "" And If .Range("C5").value <> "" Then
        Call Compliance_Heatmapping
    End If
End If

Upvotes: 0

Related Questions