Conditional Formatting Code with cell as variable (For Next)

Adrae

Active Member
Joined
Feb 19, 2002
Messages
306
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
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi

Probably because you need to include an equals sign ie:

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

Best regards

Richard
 

Forum statistics

Threads
1,137,294
Messages
5,680,660
Members
419,923
Latest member
Kalthus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top