Conditional Formatting Code with cell as variable (For Next)

[Adrae]

Hi - Does anyone know how to change this code so that when it runs it does not put quotes around the formula (code in red). After it runs, it does not change the cell as expected, when I troubleshoot why, I find that the conditional formatting on one of the cells says:

="$A$32 <> """"" (Doesn't work) instead of =$A$32 <> """" (works). Any idea how to make the macro give me the second result?


Dim ce As Range
For Each ce In Range("MyRange")
If ce <> "" Then
If ce.Offset(, 5) = "" Then
ce.Offset(, 5).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression,
Formula1:=ce.Address & " <> """""
Selection.FormatConditions(1).Interior.ColorIndex = 4
Else
ce.Offset(, 5).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:=ce.Address & " <> """""
Selection.FormatConditions(1).Interior.ColorIndex = 36
End If
Next ce

 

[Richard Schollar]

Hi

Probably because you need to include an equals sign ie:

Selection.FormatConditions.Add Type:=xlExpression, 
Formula1:="=" & ce.Address & " <> """""

Best regards

Richard

 

[Adrae]

Worked like a dream! Thanks!